New Truncation Error Message in SQL Server 2019

Thursday Feb 7th 2019 by Greg Larsen

A new error message in SQL Server 2019 will make your life much easier!? Learn more...

If you have been coding TSQL for a while you probable are familiar and frustrated with this error message:

Msg 8152, Level 16, State 30, Line 3
String or binary data would be truncated.
The statement has been terminated.

Not knowing what column and data value was truncated can be frustration! You will be happy to hear that SQL Server 2019 has improved this message to include the table and column name and the truncated value, provided you are running SQL Server 2019 CTP 2.1 and above.

Suppose you have a column named MyColumn, which is defined as a nvarchar(4), in a table name Demo.dbo.MyTable. If you were trying to insert the value "My Data" into that column, in a SQL Server 2019 table, then you would get the following error message:

Msg 2628, Level 16, State 1, Line 3
String or binary data would be truncated in table 'Demo.dbo.MyTable', column 'MyColumn'. Truncated value: 'My D'.

Now you can tell the table, column and the truncated value for the row that was truncated. SQL Server only displays the first row that was truncated, if you have multiple rows that would be truncated. You need to know that this new error message is only available in SQL Server 2019 CTP 2.1 and CTP 2.2 if you have turned on trace flag 460. Hopefully by the time SQL Server 2019 is fully baked you will no longer need to set this trace flag to receive the new truncation error message.

Mobile Site | Full Site