String or Binary Data would be Truncated

String or Binary Data would be Truncated

  If you’ve every tried to import data into SQL from another source, or even if you’ve just tried to insert data from a user, odds are you’ve hit ths annoying string or binary data would be truncated error message. I hit it at my first full time job, trying to insert data into SQL 2000. It’s a really unhelpful message. What data is going to be truncated? Which column? Which Row? What can I search for in my 10GB Text file? Give me something! After being the most upvoted item on Microsoft Connect and on it’s replacement feedback.azure, Microsoft announced earlier this year that it has a fix for it planned, and in the recent preview of SQL 2019, we can finally see some progress. Before we look at the improvement, let’s see the current behavior.

Run that script in any version of SQL, including the 2019 preview, and you’ll get the familiar error:

Msg 8152, Level 16, State 30, Line 11 String or binary data would be truncated.

Which string? In this case the column is obvious, but it usually isn’t. But in SQL 2019 and SQL 2017 CU 12, if you turn on Trace Flag 460, it all suddenly becomes obvious.

Now if we run the above insert statement again, we get a really useful message:

Msg 2628, Level 16, State 1, Line 12

String or binary data would be truncated in table ‘tempdb.dbo.Test1’, column ‘Col1’. Truncated value: ‘Long S’. How awesome is that! The error message now tells me which table is truncating data, which column is going to be truncated, and what the start of the truncated value is! This is going to make it simple to find the offending data and work out how to deal with it. It’s taken Microsoft 20 years, but finally we have a useful error message when the data doesn’t fit.