Home > SQL Server, SQL Server 2019 > SQL Server 2019 – String Truncation Error Demystified…

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

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

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: