Is there an efficient way to see the cause for "String or binary data would be truncated"?

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:

  • 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.

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


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.

Tags:

Sql Server

String

Sql Server 2017

Related

Same function in SELECT and WHERE clause What benefit is there from enabling Query Store on msdb? WHERE vs. HAVING on non-aggregate columns. Advantages / Disadvantages / Irrelevant? Filter Condition Difference - Where Clause vs Join Condition Is there any (hidden) built-in function on MS-SQL to unquote object names? What is the optimized way to pass multiple parameter from a single table to a function? High CPU system time usage on MySQL server If no plans to uninstall, can Log and Update Cache folders in Setup Bootstrap folder be removed? Should I explicitly DENY UPDATE to columns that should not be updated? How to avoid implicit type casts in PostgreSQL? In PostgreSQL, what is the difference between a "Stored Procedure" and other types of functions? What is difference between mongodump and mongoexport?

Recent Posts