SQL Server 2019 Preview (CTP 2.0) introduced a long-awaited improvement to an error message that’s been around in SQL Server for many years, but was unhelpful:
Msg 8152, Level 16, State 6, Procedure ProcedureName, Line Linenumber String or binary data would be truncated.
Thanks for nothing, error message. Which table? Which column? What data?
This is how the error message looks now:
Msg 2628, Level 16, State 6, Procedure ProcedureName, Line Linenumber String or binary data would be truncated in table '%.*ls', column '%.*ls'. Truncated value: '%.*ls'.
Notice how the table, column and value are all mentioned in the error message now, which makes debugging and troubleshooting much easier. Thank you Microsoft!
As of 24 October 2018, we can now get the full picture in SQL Server 2017 as well, provided we install Cumulative Update 12. I’d say this is worth the update in and of itself!
How to make use of this new information
Microsoft has a long-standing policy about maintaining backward compatibility, so turning on this feature is optional, enabled with Trace Flag 460. Here’s how to do it on both Windows and Linux:
SQL Server 2017 on Windows
On Windows, we can enable a Trace Flag using SQL Server Configuration Manager. If we’re on Windows 10 or Windows Server 2016, we might need to manually locate Configuration Manager.
Under SQL Server Services, locate the SQL Server service, right-click on it, and choose Properties.
Go to the Startup Parameters tab, and type -T460 (note that there is no space between -T and the Trace Flag number).
Click Add, and then click Apply. We should see the Trace Flag in the list of Existing Parameters now.
After clicking OK, the final step is to restart the SQL Server service for the change to take effect. We can do this by right-clicking on the SQL Server service again, and choosing Restart. This will also restart the SQL Server Agent if it is running.
SQL Server 2017 on Linux
On SQL Server on Linux (note that this includes SQL Server in Docker containers), we can add a Trace Flag to the SQL Server instance using the command line tool mssql-conf.
From the command line of our SQL Server instance, run the following command to add Trace Flag 460.
sudo /opt/mssql/bin/mssql-conf traceflag 460 on
As with Windows, we need to restart the SQL Server service to effect this change.
sudo systemctl restart mssql-server
What about older versions of SQL Server?
Although it is not available in SQL Server 2016 yet, we might expect this in the near future. I certainly wouldn’t hold my breath for SQL Server 2014 and older versions.
And remember, SQL Server 2008 and 2008 R2 are at their end of life in July 2019. If you have not upgraded to at least SQL Server 2014 by now, you have a lot of work ahead of you for the next two years.
Share your thoughts about SQL Server error messages in the comments below.
Sorry, not clear how Table ‘%.*ls’ Column ‘%.*ls’ is meaningful. Can you clarify?
Those are substitutions in the error message, Jim. When you see the actual error, it will substitute out the table and column name instead.
hi, it’s real interresting, that a change like this need’s many years and on other side, that this reflect to blog posts. (this has nothing to do with you!) i think and hope that other stuff from sql server will be used to write on blogs because it really improve the nature from sql server. but if you look into connect from sql server it looks as a big black hole where nearly nothing come back (no feedback, no change, nothing!). it’s real frustrating to work with sql server.
Connect was taken away and replaced with UserVoice, most likely because of GDPR implications. I’m sorry your experience hasn’t been great but there is a ton of information out there. Let me know where you need help looking.
thanks for your words! somtimes it’s hard to understand, why it’s required, that many people fight with a problem like this, poor error msg. yes, i’m quite sure, that you find many pages in www around this. but should such a problem not solved by the software vendor asap instead of let many people fight with this? this is anoing.
small similar sample:
declare @char char(1) = N’abc’;
print @char;
you know the result! you can see some page around this and also some horrible workaround to find a way to handle it.
but this sooo horrible, that there should be only one page with infos around this –> release notes from patch. and nothing else
i can give you many examples like this, where sql server is not well designed and also the language tsql is something like 80% defined and developed. and the last 20% we don’t fix and every, who work hard with this database, should handle this limitations.
and one of the bades news in the last days: ssms 18 without debugging and diagramms –> in blog from ms. diagrams –> use visio or 3th party. and no answer why ms remove the debugging functionality from a great tools like ssms. if they belive, that i write bug free code, when i don’t have a debugger –> i hope this will happen. (but i don’t think so)
but there exists also many good thing aournd sql server, but i see always changes in some corners without take care about the big picture from sql server.
Thank you, Randolph. An excellent explanation on setting this up.
A lot of what I do is data migrations, often from unstructured data. This message has been the bane of my existence since SQL Server was a Sybase product. I’m happy that they are finally making it into something useful.
Hallelujah.
Now if MS will just fix IIS to do the same thing for the “Object reference not set to an instance of an object.” We’ll be all set.
Null references in .NET are a whole conversation in and of themselves, but at least that comes with a stack trace. Thanks for commenting!
Note this does not work with TABLE variables. It does work with Temp tables.
Thanks for your comment! Have you considered opening a bug for table variables? That would be useful.
Done: https://feedback.azure.com/forums/908035-sql-server/suggestions/36030649-new-string-or-binary-data-would-be-truncated-err
Comments are closed.