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.
create table StringTruncation (A int, B varchar(10), C nvarchar(5), D nvarchar(max), E datetime)
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.
-- First move the table out of the way exec sp_rename StringTruncation, StringTruncation_; -- cover it with a query create view dbo.StringTruncation with schemabinding as select A, convert(Nvarchar(max),B) B, convert(Nvarchar(max),C) C, D, E from dbo.StringTruncation_ GO -- use a trigger to allow INSERTs, with the length checks thrown in create trigger dbo.trig_ioi_StringTruncation on StringTruncation instead of insert as 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 end from inserted where len(C) > 5 or len(B) > 10 GO -- keep good data if @@rowcount = 0 insert StringTruncation_ select * from inserted else raiserror(@offending,16,1) GO
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.