I was pleased to see the fix for the string or binary data truncated error saw some development work in SQL Server 2019. If you haven’t read about this, the changes are described on MSDN and I thanked the development team. What’s even better news is that the fix has been ported to SQL Server 2017 in CU12 is is also slated to appear in an upcoming SQL Server 2016 CU.
In working on this error, Microsoft provided some guidance about where the first occurrence of the truncation would occur, in the column and which data value. This is invaluable in troubleshooting data movement, though this could be a trial and error process if you have lots of possible errors in different rows and columns. I could see us working our way through the list of problem data cell by cell.
Microsoft didn’t change the error message, as that might have broken code. Instead, we get a new error message, number 2628, which allows us to slowly rev our error handling code. To me, this was a much better solution than merely revving the old error. We have a trace flag to turn the behavior on and off, so we can work through the places our code is affected.
The plan is that at some point this will become default behavior, and error 2628 will replace that the all too familiar and frustrating error 8152. That makes sense, and I like the idea of limiting how long we can control behavior of our systems. While we might have code that needs to be fixed, we need to fix the code, and sooner rather than endlessly deferred. Even if you have an existing SQL Server 2016 or 2017 system, I’d hope that you’d be looking to patch your system at some point, perhaps to a level where this is the default behavior.
There are many reasons why and when we patch, but we might be forced to because of some security issue. If that happens, I hope you’ve been working to fix code that is looking for error 8152 and are prepared to have SQL Server behavior changed.