Improved Error message: String or binary data would be truncated.(SQL Server 2019)

Before SQL 2019, it is really frustrated with this error message because you cannot find which column is the offending column.
The error message is informative now in SQL Server 2019, which tells you which column is causing the issue.
Here is an example:

  
 
create table test (id int, col1 varchar(10),
col2 varchar(10),
col3 varchar(10))

insert into test values(1,'abc','cde','fgh')

insert into test
select id, col1,col2,col3+col3+col3+col3 
from test


select * from test
drop table test 

  

/*
Msg 2628, Level 16, State 1, Line 7
String or binary data would be truncated in table ‘test1.dbo.test’,
column ‘col3’. Truncated value: ‘fghfghfghf’.
The statement has been terminated.

*/

If you run the same setup in previous SQL instances, you will get the below message:
/*

(1 row affected)
Msg 8152, Level 16, State 14, Line 7
String or binary data would be truncated.
The statement has been terminated.

*/

Let’s migrate up to SQL Server 2019 to welcome this improvement.

String or Binary data would be truncated: replacing the infamous error 8152


Dynamic Pivot with Case Expression(using for xml or string _agg)

  
 
CREATE TABLE [dbo].[ShoppingDetail](
	[CustomerName] [varchar](256) NOT NULL,
	[ProductCategory] [varchar](256) NOT NULL,
	[TotalAmount] [numeric](18, 2) NOT NULL,
	[Date] [date] NULL
)

INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'Roy Martin', N'Clothing', CAST(2967.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
,(N'Roy Martin', N'Accessories', CAST(2458.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
, (N'Roy Martin', N'Bikes', CAST(52478.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
, (N'Caitlin C Watson', N'Clothing', CAST(3289.00 AS Numeric(18, 2)), CAST(N'2019-12-11' AS Date))
, (N'Caitlin C Watson', N'Bikes', CAST(75769.00 AS Numeric(18, 2)), CAST(N'2019-12-14' AS Date))
,(N'Taylor Torres', N'Clothing', CAST(2875.00 AS Numeric(18, 2)), CAST(N'2019-12-14' AS Date))
,(N'Taylor Torres', N'Accessories', CAST(6567.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
,(N'Taylor Torres', N'Bikes', CAST(46897.00 AS Numeric(18, 2)), CAST(N'2019-12-11' AS Date))
, (N'Taylor Torres', N'Food Products', CAST(15783.00 AS Numeric(18, 2)), CAST(N'2019-12-14' AS Date))

declare @ColumnHeaders NVARCHAR(4000) ;
declare @ColumnHeaders2 NVARCHAR(4000) ;
declare  @sql NVARCHAR(4000);
declare @Param int=7  --day
-- --===== Create number table on-the-fly
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n <101), 
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num1
WHere n<= @Param ) ---Control the total columns list
,dates as (
Select n, DATEADD(day, -n+1, (Select max([date]) from ShoppingDetail) ) dt  
from(Select n from nums) D(n)
)
 
Select @ColumnHeaders = STUFF( (SELECT ',' + 'SUM (Case when [Date]='+ quotename( Convert(char(8),dt,112),'''') +' then  TotalAmount  else null end) as ' + Quotename(Convert(char(10),dt,120),'[')  + char(10)+char(13)
FROM  dates   
Order by n desc
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')   
,
@ColumnHeaders2 = STUFF( (SELECT '+' + 'isnull(SUM (Case when [Date]='+ quotename( Convert(char(8),dt,112),'''') +' then  TotalAmount  else null end),0) '   + char(10)+char(13)
FROM  dates   
Order by n desc
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')   
 
 From dates t1


  
SET @sql = N'Select  Case when [CustomerName] is null then ''Total'' else [CustomerName] end [CustomerName], '
+ @ColumnHeaders + ','+@ColumnHeaders2+' as [total] '
+' from [ShoppingDetail]
Group by grouping sets(CustomerName,())  '
  
--print @sql
 EXEC sp_executesql @sql;
     

drop table [ShoppingDetail]


--SQL Server 2017 or 2019



CREATE TABLE [dbo].[ShoppingDetail](
	[CustomerName] [varchar](256) NOT NULL,
	[ProductCategory] [varchar](256) NOT NULL,
	[TotalAmount] [numeric](18, 2) NOT NULL,
	[Date] [date] NULL
)

INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'Roy Martin', N'Clothing', CAST(2967.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
,(N'Roy Martin', N'Accessories', CAST(2458.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
, (N'Roy Martin', N'Bikes', CAST(52478.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
, (N'Caitlin C Watson', N'Clothing', CAST(3289.00 AS Numeric(18, 2)), CAST(N'2019-12-11' AS Date))
, (N'Caitlin C Watson', N'Bikes', CAST(75769.00 AS Numeric(18, 2)), CAST(N'2019-12-14' AS Date))
,(N'Taylor Torres', N'Clothing', CAST(2875.00 AS Numeric(18, 2)), CAST(N'2019-12-14' AS Date))
,(N'Taylor Torres', N'Accessories', CAST(6567.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
,(N'Taylor Torres', N'Bikes', CAST(46897.00 AS Numeric(18, 2)), CAST(N'2019-12-11' AS Date))
, (N'Taylor Torres', N'Food Products', CAST(15783.00 AS Numeric(18, 2)), CAST(N'2019-12-14' AS Date))

declare @ColumnHeaders NVARCHAR(4000) ;
declare @ColumnHeaders2 NVARCHAR(4000) ;
declare  @sql NVARCHAR(4000);
declare @Param int=7  --day
-- --===== Create number table on-the-fly
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n <101), 
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num1
WHere n<= @Param ) ---Control the total columns list
,dates as (
Select n, DATEADD(day, -n+1, (Select max([date]) from ShoppingDetail) ) dt  
from(Select n from nums) D(n)
)
 
Select @ColumnHeaders = string_agg('SUM (Case when [Date]='+ quotename( Convert(char(8),dt,112),'''') +' then  TotalAmount  else null end) as ' + Quotename(Convert(char(10),dt,120),'[')  , ','+char(10)+char(13))  WITHIN GROUP ( ORDER BY dt)
,@ColumnHeaders2 = string_agg('isnull(SUM (Case when [Date]='+ quotename( Convert(char(8),dt,112),'''') +' then  TotalAmount  else null end),0) ' , '+'+char(10)+char(13))  WITHIN GROUP ( ORDER BY dt)
From dates  

SET @sql = N'Select  Case when [CustomerName] is null then ''Total'' else [CustomerName] end [CustomerName], '
+ @ColumnHeaders + ','+@ColumnHeaders2+' as [total] '
+' from [ShoppingDetail]
Group by grouping sets(CustomerName,())  '
  
 EXEC sp_executesql @sql;
     

drop table [ShoppingDetail]

  

https://forums.asp.net/p/2162731/6289179.aspx?p=True&t=637130305269355006


Split String Based on a Pattern

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1748ac46-6654-40e9-85b9-d3c812532173/split-text-in-query?forum=transactsql

  
 
create table #test(Interaction int,InteractionLog varchar(8000))


insert into #test values(10456
,'12:26:47: Initializing 12:26:47: Offering 12:26:47: ANI:  1111111111 12:26:47: DNIS:  1713 12:26:47: Call answered 12:26:47: Company IVR 12:26:47: Default Schedule 12:26:48: CompanyIVR 12:26:48: Company IVR 12:26:56: PhoneNumEntered:1111111111 12:27:29: AuthViaANI=Success 12:28:24: Entered Workgroup Customer_Service 12:28:24: Offering 12:28:50: Assigned Skill: Customer_Service 12:28:50: ACD - Wait Agent 12:28:50: ACD call waiting for agent 12:29:21: ACD interaction assigned to AnAgent 12:29:21: ACD - Alerting: AnAgent 12:29:22: Sent to user AnAgent 12:29:22: Offering 12:29:23: Sent to station S-STATION-NUM 12:29:23: Connected 12:29:23: ACD interaction connected to AnAgent 12:29:23: ACD - Assigned: AnAgent 12:33:12: Disconnected [Remote Disconnect:Normal, Specified (ISDN Cause Code 31)]')
 
 declare  @search_expression NVARCHAR(4000) = '%_[0-9][0-9]:[0-9][0-9]:[0-9][0-9]:%' 
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n <101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y), 
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
    

,positions as 
(
select distinct n = subIdx + n 
, InteractionLog,Interaction	 
 from #test
	CROSS APPLY (select n FROM Nums ) d
        CROSS APPLY (SELECT numRev = LEN(InteractionLog) - n + 1) x
        CROSS APPLY (SELECT subExp = RIGHT(InteractionLog, numRev)) y
        CROSS APPLY (SELECT subIdx = PATINDEX(@search_expression, subExp)) z
    WHERE 
        n BETWEEN 1 AND LEN(InteractionLog)
 AND SubIdx  0

		)
 , 
 finalset as (
Select 
 Interaction, n,
 substring(InteractionLog,isnull(lag(n) over(order by n),1), n-isnull(lag(n) over(order by n) ,0)) InteractionLog 
from positions
union  
Select top 1 Interaction,n,substring(InteractionLog,max(n)Over(Partition by Interaction), len(InteractionLog)) Interaction
from positions
Order by n desc
)


select Interaction,InteractionLog, 
row_number() over(partition by Interaction  order by n ) InteractionSeq
 from finalset

drop table if exists #test

--https://dba.stackexchange.com/questions/41961/how-to-find-all-positions-of-a-string-within-another-string

  

Where is SSIS Menu in Visual Studio 2019? Found It Finally.

It took me a while to look around for “SSIS Menu” from VS 2019.
Finally after a long hour, I found it that it is hiding under Extensions Tab>>SSIS>…

I need to use this Tab to finish a walkthrough for a tutorial from here:

https://docs.microsoft.com/en-us/sql/integration-services/lesson-3-2-adding-and-configuring-logging?view=sql-server-ver15

Visual Studio Enterprise 2019: version 4.8.03752
SQL Server Integration Services Projects: version 3.3.2


Find End of Previous Fiscal Year (End of first quarter 3/31) T-SQL

To find the end date of 3/31 in a previous fiscal year for any End of Fiscal Year (End of first quarter in calendar: 3/31)
For example any data om the range: 2017-04-01 through 2018-03-31: 2017-03-31
2018-04-01 through 2019-03-31: 2019-03-31
2019-04-01 through 2020-03-31: 2019-03-01

  

 declare @dt date='2019-10-10'
Select datepart(quarter,@dt),
Dateadd(day,-1,
dateadd(quarter,1+(datediff(quarter,0,@dt))
+Case when datepart(quarter,@dt)>1 
then 1-datepart(quarter,@dt) else -4 end,0))


--or

select CASE
  WHEN MONTH(@dt) BETWEEN 1  AND 3  THEN  datefromparts(YEAR(@dt) - 1,3,31)
 Else   
    datefromparts(YEAR(@dt),3,31)
 END  

  

Add Year to End of Month Date

When you have a need to bump a end of month date to another, it is pretty handy to use dateadd function with year=1. But this code could create a wrong date 2/28 if the next year is a leap year. It should be 2/29.

To make this code work correctly, you can wrap the dateadd function with another function EOMONTH to guarantee the result is correct.
See snippet below:

  
 

Declare @dt date=EOMONTH(getdate())

Select dateadd(year,1,@dt),--incorrect when next year is leap year.

EOMONTH(dateadd(year,1,@dt))  
--USE EOMONTH Function to handle Feburary in LEAP year

  

Dynamic Pivot Last 12 Months’ Occurrence With CASE Expression

  


 CREATE TABLE TEST_TABLE
(
AccountID NVARCHAR(12)
,AccountName NVARCHAR(12),
AddedOn DATETIME
)
INSERT INTO TEST_TABLE VALUES ('183127266','Test1','2019-01-01')
,('183127266','Test1','2019-01-02')
,('892555666','Test2','2019-03-01')
,('892555666','Test2','2019-03-02')
,('892555666','Test2','2019-04-01')
,('503787421','Test3','2019-04-02')
,('503787421','Test3','2019-04-03')
,('503787421','Test3','2019-04-04')
,('503787421','Test3','2019-05-01')
,('742780278','Test4','2019-05-01')
,('742780278','Test4','2019-05-01')
,('742780278','Test4','2019-09-01')
,('466365991','Test5','2019-09-01')
,('466365991','Test5','2019-10-01')
,('664059982','Test6','2019-10-01')
,('664059982','Test6','2019-11-01')
,('372853081','Test7','2019-12-01');

 
  
declare @ColumnHeaders NVARCHAR(4000) ;
declare  @sql NVARCHAR(4000);
declare @Param int=12
 
-- --===== Create number table on-the-fly
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n <101), 
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num1
WHere n<= @Param ) ---Control the total columns list
,dates as (
Select n, DATEADD(Month, -n+1, Dateadd(month,datediff(month,0,getdate()),0) ) dt  
from(Select n from nums) D(n)
)
   
Select @ColumnHeaders = STUFF( (SELECT ',' + 'isnull(SUM(Case when format(AddedOn,''MMM-yyyy'')='+ quotename(format(dt,'MMM-yyyy'),'''') +' then  1  else null end), 0) as ' + Quotename(format(dt,'MMM-yyyy'),'[')  + char(10)+char(13)
FROM  dates
Order by n desc
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
   
 --print @ColumnHeaders
 
      SET @sql = N'Select AccountID, AccountName, '
+ @ColumnHeaders 
+' from TEST_TABLE
Group by AccountID, AccountName '
 
--print @sql
 EXEC sp_executesql @sql;
   
 

drop table TEST_TABLE

/*
AccountID	AccountName	Jan-2019	Feb-2019	Mar-2019	Apr-2019	May-2019	Jun-2019	Jul-2019	Aug-2019	Sep-2019	Oct-2019	Nov-2019	Dec-2019
183127266	Test1	2	0	0	0	0	0	0	0	0	0	0	0
892555666	Test2	0	0	2	1	0	0	0	0	0	0	0	0
503787421	Test3	0	0	0	3	1	0	0	0	0	0	0	0
742780278	Test4	0	0	0	0	2	0	0	0	1	0	0	0
466365991	Test5	0	0	0	0	0	0	0	0	1	1	0	0
664059982	Test6	0	0	0	0	0	0	0	0	0	1	1	0
372853081	Test7	0	0	0	0	0	0	0	0	0	0	0	1
*/

  

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9055d6a8-23b4-45b5-8519-5815e343ff9a/dynamics-date-in-pivot-sql?forum=transactsql


Count Whole Month Difference Between Two Dates

 create table test (startDate date, endDate date)
insert into test values
('2019-11-04','2019-12-05')
,('2019-11-04','2019-12-04')
,('2019-10-04','2019-12-05')
,('2019-10-04','2019-12-04')
,('2019-09-04','2019-12-05')
,('2019-09-04','2019-12-04')

Select startDate,endDate,
Case when Datediff(month,startDate,endDate)0
then
DATEDIFF(MONTH
, DATEADD(DAY,-DAY(startDate)+1,startDate)
,DATEADD(DAY,-DAY(startDate),endDate))
Else
0
End
from test

drop table test

/*
startDate	endDate	delta
2019-11-04	2019-12-05	1
2019-11-04	2019-12-04	0
2019-10-04	2019-12-05	2
2019-10-04	2019-12-04	1
2019-09-04	2019-12-05	3
2019-09-04	2019-12-04	2
*/
  

https://forums.asp.net/t/2162129.aspx?count+months+query

https://stackoverflow.com/questions/1106945/calculating-number-of-full-months-between-two-dates-in-sql


Search Text Inside a Trigger

When you search within a trigger with long code, you should use search
on text from sql_modules table's definition column.
  

   SELECT
Tables.Name TableName,
 Triggers.name TriggerName,
 Triggers.crdate TriggerCreatedDate,
 m.definition TriggerText
 ,len(m.definition) triggerLen
FROM  sysobjects Triggers
 Inner Join sysobjects Tables
 On Triggers.parent_obj = Tables.id
 INNER JOIN sys.sql_modules AS m
ON Triggers.id = m.[object_id]

WHERE Triggers.xtype = 'TR'
      And Tables.xtype = 'U'
	  and m.definition like'%Searchtext%'
 Order by 1,2