SQL Server 2019 – String Truncation Error Demystified…

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

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

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 !!!

Author: Arunraj

I am a Microsoft Certified Technology Specialist (Database Developer). I work on SQL Server programming since SQL Server 7.0 specializes in SQL Server Programming and Performance Tuning and has 14 years of hands-on experience. I hold a Master Degree in Computer Applications. I am also part of NJSQL User Group and Northern New Jersey .Net User Group. View all posts by Arunraj