SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Revving the Error

By Steve Jones,

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.

 
Total article views: 45 | Views in the last 30 days: 45
 
Related Articles
FORUM

Insert Trigger Behavior Change?

Trigger changed behavior when moved to SQL2005 from SQL2000.

FORUM

SQL server 2005 difFerente behavior?

SQL server 2005 difFerente behavior?

FORUM

Application errors might be related to SQL

Application errors might be related to SQL

BLOG

Denali – Day 31: Discontinue and Breaking/Behavior change features

Denali – Day 31: Discontinue and Breaking/Behavior change features Discontinue Features: As discusse...

ARTICLE

Enable Trace Flags in SQL Server

Learn how to enable trace flags to change the behavior of your SQL Server instance.

Tags
editorial    
 
Contribute