Why isn't "String or Binary data would be truncated" a more descriptive error?

It turns out there's an open "feature request" for this on MS Connect - I'd encourage you to vote for it if you'd like the functionality changed.

https://connect.microsoft.com/SQLServer/feedback/details/339410/

ADDED:

It actually looks like there's another request for this same feature (though poorly named) that's been outstanding since Yukon's development in 2005 that I'd encourage people to vote for as well:

https://connect.microsoft.com/SQLServer/feedback/details/125347/

Update 2016

It seems Microsoft has tried to delete evidence of this bug's true age. Fair enough. Find the old site archived here.

After not finding an acceptable answer anywhere I came up with the following:

1. Get the query that is causing the problems (you can also use SQL Profiler if you dont have the source)
2. Remove all WHERE clauses and other unimportant parts until you are basically just left with the SELECT and FROM parts
3. Add WHERE 0 = 1 (this will select only table structure)
4. Add INTO [MyTempTable] just before the FROM clause

You should end up with something like

SELECT Col1, Col2, ..., [ColN]INTO [MyTempTable]FROM  [Tables etc.]WHERE 0 = 1

This will create a table called MyTempTable in your DB that you can compare to your target table structure to see where they differ i.e. you can compare the columns on both tables.

EDIT: You can compare the data types and column sizes of each column on the original table and MyTempTable to see where they differ.All column names in your new table will be the same as the old, and the data types and sizes will be the same EXCEPT where the offending column is. In other words, with this query, SQL will automatically create columns that are large enough to handle the largest possible entry from the source table

Answering a DUP that got closed, so answering here instead. This pattern can be used, if somewhat elaborate, but it can be useful when it is not trivial to change the application, or set up profiler to see what is happening. Sometimes, you just need the error to propagate to the APP itself so you can see right from the APP, the correct and useful error message.

In those cases, a quick poke into the DB with this solution will save you lots of time. Save it as a template, and make quick changes to it to solve this problem on any table.

The problem

Sample table

create table StringTruncation(A int, B varchar(10), C nvarchar(5), D nvarchar(max), E datetime)

Sample statement

insert StringTruncation values(1, '0123456789', 'abcdef', 'This overflows on C', GETDATE())

The dreaded useless error

Msg 8152, Level 16, State 4, Line 1String or binary data would be truncated.The statement has been terminated.

The example shows only 2 columns where it could overflow, but imagine if it were 20 columns, or 40.

The solution

-- First move the table out of the wayexec sp_rename StringTruncation, StringTruncation_;-- cover it with a querycreate view dbo.StringTruncationwith schemabindingasselect    A,    convert(Nvarchar(max),B) B,    convert(Nvarchar(max),C) C,    D, Efrom dbo.StringTruncation_GO-- use a trigger to allow INSERTs, with the length checks thrown increate trigger dbo.trig_ioi_StringTruncationon StringTruncationinstead of insertasset nocount ondeclare @offending nvarchar(max)select TOP 1 @offending = case    when len(C) > 5 then 'Data too long for Column [C] Size 5: ' + C    when len(B) > 10 then 'Data too long for Column [D] Size 10: ' + B    endfrom insertedwhere len(C) > 5 or len(B) > 10GO-- keep good dataif @@rowcount = 0    insert StringTruncation_    select * from insertedelse    raiserror(@offending,16,1)GO

Test it

insert StringTruncation values(1, '0s123456789', 'abcde', 'This overflows on C', GETDATE())

Result

Msg 50000, Level 16, State 1, Procedure trig_ioi_StringTruncation, Line 18
Data too long for Column [D] Size 10: 0s123456789

(1 row(s) affected)

Notes

• It needs a mirror trigger for UPDATEs
• It will currently only report on the first offending record-column. It is possible to report more than one record-column, but I believe that is actually counter-productive.