13

This is a follow-up on this question. It is also related to this feature request from Microsoft.

However, many years have passed and several major releases reached the market since it was reported.

Question: does SQL Server 2017 provide any mechanism to ease finding out the root cause of this error? Or it is as hard to investigate as it was about 9 years ago when the issue was reported?

  • I spend at least 4 hours a week on debugging this single error and SQL isn't even in my job-description. – Tonny Jan 25 '18 at 17:01
20

There has been no change. SQL Server 2017 still offers up the same vague error message and does not provide any mechanism to discover the offending row/column.

This Connect item had over 1,600 votes when Connect was retired:

The latest comment there, from Microsoft, was this:

Latest update - the developer working on it understands the challenges involved in creating a full fix. It may be tricky to plumb the information about columns needed to generate a full error message down to the actual conversion function in such a way that won't impact insert or update performance. We may implement something cheap in the short term such as logging the type and length of the data being truncated. It's still too early to know when such a fix would reach a publicly visible release.

The best I can suggest is to vote for that item (and comment on it, describing the business value this would add for you).

  • +1. To add one of less inefficient ways to find out the culprint (if you don't have an app log) is to use the profiler and filter (by host/user/app/query/etc). Also this can be usefull: nimblegecko.com/… – jean Jul 13 '18 at 14:49
2

Our prayers have been answered as indicated by this MDSN blog post:

SQL Server 2019 introduces a new message, with additional context information.

The error should be much more meaningful now:

Msg 2628, Level 16, State 1, Line 14 String or binary data would be truncated in table 'DbName.SchemaName.TableName', column 'Col'. Truncated value: '...'.

Also, the good news is that this change will be back-ported in both 2017 and 2016:

This new message is also backported to SQL Server 2017 CU12 (and in an upcoming SQL Server 2016 SP2 CU), but not by default. You need to enable trace flag 460 to replace message ID 8152 with 2628, either at the session or server level.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.