Introduction
I’m sure lots of us who have been developing in SQLServer over ages would agree to the fact that the error
String or binary data would be truncated.
The statement has been terminated.
is one of the most frustrating errors you would have ever come across in T-SQL. Especially in case of long stored procedures with lengthy INSERT…SELECT statements it was always a daunting to task to find the column which acted as the root cause for the above error. And most times the error happens at a later stage due to absence of any breaking data at the time of implementation.
A good majority of senior developers have always complained against this ambiguity and multiple connect items were logged for this issue which got pretty good support as well.
Implementation
When feedback platform was moved to feedback.azure.com Microsoft had opened a request in that for the connect requests and this had also got good number of votes
Accordingly MS started background work to fix this and finally the fix was released in SQL 2019 version to enhance the error message to include more information. Based on this, the error message has been modified to the below
String or binary data would be truncated in table 'XXXXXXXXXX', column 'YYYYYYY'. Truncated value: 'ZZZZZZ'.
The statement has been terminated.
This was really a good news for all of us, but still there was a small concern that we have wait a while to see this in action as most of the currently implemented instances were on SQL 2016 and SQL 2017.
But it seems MS read our mind on this and now I’m really happy to see the announcement that this enhancement has been backported to SQL 2017 CU 12 and on SQL 2016 SP2 CU.
To enable this currently a trace flag has also being introduced (trace flag 460) which can be enabled at session level or at server level itself. Once set it replaces the older error message with the new one above for truncation exceptions raised. The future SQL 2019 releases should have this message as the default and wont require setting the trace flag explicitly for this.
Illustration
Lets see an illustration of the above error message on a sample table
The code will look like below
–setting the trace flag for the session
DBCC TRACEON (460);
GO
–sample table
declare @t table(
v varchar(10)
)
— insert values
insert @t
values
(‘test’),
(‘this is a long value to check for truncation error’) — this raises the error
Now lets check the result
As expected we will get the new error message which gives clear indication of the table, column and value which caused the truncation exception. This makes it much easier for someone to debug and fix the issue.
Now if you turn off the trace flag and try, you can see that it reverts to the old error message
Conclusion
As seen from the above illustration, this new enhancement on truncation error is really a life saver for someone developing or debugging Transact SQL code and is sure to save many hours of development effort on truncation issues which is one of the high frequent issues we come across in ETL, datawarehousing projects.
The official announcement regarding the release can be found in the below link
Let me end this article by conveying big thanks to Pedro Lopes (@SQLPedro) – Senior Program Manager and the entire MS team for the help and support provided in addressing this issue and coming up with the release.
