Improved Error message: String or binary data would be truncated.(SQL Server 2019)
Posted: December 30, 2019 Filed under: Uncategorized Leave a commentBefore 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.
Dynamic Pivot with Case Expression(using for xml or string _agg)
Posted: December 27, 2019 Filed under: Uncategorized Leave a comment
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
Posted: December 20, 2019 Filed under: Uncategorized Leave a comment
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.
Posted: December 18, 2019 Filed under: Uncategorized 5 CommentsIt 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:
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
Posted: December 17, 2019 Filed under: Uncategorized Leave a commentTo 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
Posted: December 6, 2019 Filed under: Uncategorized Leave a commentWhen 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
Posted: December 4, 2019 Filed under: Uncategorized Leave a comment
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
*/
Count Whole Month Difference Between Two Dates
Posted: December 4, 2019 Filed under: Uncategorized Leave a comment
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
Search Text Inside a Trigger
Posted: December 2, 2019 Filed under: Uncategorized Leave a commentWhen 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
Jingyang Li 