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?

Tom V
  • 15,752
  • 7
  • 66
  • 87
Alexei
  • 1,191
  • 1
  • 14
  • 36

2 Answers2

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 (and the new item in Azure Feedback was not ported when they moved the content yet again):

  • Please fix the "String or binary data would be truncated" message to give the column name

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.

Since then, this has been fixed (as later answers and comments suggest). To get the more verbose message (stealing shamelessly from Brent's post):

SQL Server 2016 SP2 CU6+, 2017 CU12+, 2019:

-- server-level:

DBCC TRACEON(460, -1);


-- query-level:


... OPTION (QUERYTRACEON 460);

SQL Server 2019

-- 2019 adds a database-level option:

ALTER DATABASE SCOPED CONFIGURATION 
  SET VERBOSE_TRUNCATION_WARNINGS = ON;

However, be careful with 460, as this bug is still under review.

Aaron Bertrand
  • 181,950
  • 28
  • 405
  • 624
4

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.

Alexei
  • 1,191
  • 1
  • 14
  • 36