To begin: I realize what this error means - I am not trying to solve a clear case of it.

This error is infamously hard to trobleshoot and fix, because when you get it placing millions of rows right into a table 100 posts wide, there's without any way to determine which column of the items row is leading to the mistake - you need to modify your process to place one row at any given time, after which see which fails. This is a discomfort, to say the least.

Can there be any reason why the mistake does not look a lot more like this?

String or Binary data would be truncated
Error inserting value "Some 18 char value" into SomeTable.SomeColumn VARCHAR(10)

That will turn it into a lot simpler to locate and proper the worthiness, otherwise the table structure itself. If seeing the table information is a burglar concern, maybe something generic, like giving the size of the attempted value and also the title from the failing column?

It works out there's a wide open "feature request" with this on MS Connect - I'd encourage you to definitely election for this if you want the functionality transformed.


It really appears like there's another request this same feature (though poorly named) which has been outstanding since Yukon's rise in 2005 that I'd persuade folks to election as well:

Responding to a DUP that got closed, so responding to here rather. This pattern may be used, if somewhat elaborate, but it may be helpful when it's not trivial to alter the applying, or setup profiler to determine what's happening. Sometimes, you simply need the mistake to propagate towards the Application itself so that you can see from the Application, the right and helpful error message.

In individuals cases, a fast poke in to the DB with this particular solution could save you time and effort. Save it as being a template, making quick changes into it to resolve this issue on any table.

The issue

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 1
String or binary data would be truncated.
The statement has been terminated.

The example shows only 2 posts where it might overflow, but let's suppose it were 20 posts, or 40.

The answer

-- First move the table out of the way
exec sp_rename StringTruncation, StringTruncation_;

-- cover it with a query
create view dbo.StringTruncation
with schemabinding
    convert(Nvarchar(max),B) B,
    convert(Nvarchar(max),C) C,
    D, E
from dbo.StringTruncation_

-- use a trigger to allow INSERTs, with the length checks thrown in
create trigger dbo.trig_ioi_StringTruncation
on StringTruncation
instead of insert
set nocount on
declare @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
from inserted
where len(C) > 5 or len(B) > 10

-- keep good data
if @@rowcount = 0
    insert StringTruncation_
    select * from inserted

Test drive it

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


Monosodium glutamate 50000, Level 16, Condition 1, Procedure trig_ioi_StringTruncation, Line 18
Data too lengthy for Column [D] Size 10: 0s123456789

(1 row(s) affected)


  • It requires one trigger for UPDATEs
  • It'll presently only set of the very first problem record-column. You'll be able to report several record-column, however i believe that's really counter-productive.

Microsoft is lazy?

It's not necessary to try each row place individually, incidentally. Just query for optimum(len(area)) for every text column, beginning using the ones you think may be the reason.