Archive

Archive for October, 2018

SQL Server 2019 – String Truncation Error Demystified…

October 3, 2018 Leave a comment

Before SQL Server 2019, String Truncation error is big mystery in SQL Server, especially if you have multiple String fields used in your Stored Procedure, it will hard to find out which field its failing and which value is causing the issue

See, Sample screenshot of String truncation error from SQL Server 2017 or before

image

In SQL Server 2019 CTP, If you enable DBCC TRACE FLAG 460, the String truncation error will now display the column name and truncated value which is causing the error

Sample Code for SQL Server 2019

DBCC TRACEON(460)

CREATE TABLE tmpDemo
 (
     intPK INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
     strSmallField VARCHAR(20)
 )

INSERT INTO [tmpDemo]
 (
     [strSmallField]
 )
 VALUES
 ('small string'),
 ('This is a large string')

DROP TABLE [tmpDemo]

Screenshot of Error message from SQL Server 2019

image

I believe this will be included as part of the main release without having to enable Trace flag 460, This will be significantly helpful for all SQL developers !!!

%d bloggers like this: