with - what are the sql error handling options available before and after 2005
Why isn't “String or Binary data would be truncated” a more descriptive error? (6)
To start: I understand what this error means - I'm not attempting to resolve an instance of it.
This error is notoriously difficult to troubleshoot, because if you get it inserting a million rows into a table 100 columns wide, there's virtually no way to determine what column of what row is causing the error - you have to modify your process to insert one row at a time, and then see which one fails. That's a pain, to put it mildly.
Is there any reason that the error doesn't look more like this?
String or Binary data would be truncated Error inserting value "Some 18 char value" into SomeTable.SomeColumn VARCHAR(10)
That would make it a lot easier to find and correct the value, if not the table structure itself. If seeing the table data is a security concern, then maybe something generic, like giving the length of the attempted value and the name of the failing column?
After not finding an acceptable answer anywhere I came up with the following:
- Get the query that is causing the problems (you can also use SQL Profiler if you dont have the source)
- Remove all WHERE clauses and other unimportant parts until you are basically just left with the SELECT and FROM parts
- Add WHERE 0 = 1 (this will select only table structure)
- 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.
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 columns where it could overflow, but imagine if it were 20 columns, 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
insert StringTruncation values (1, '0s123456789', 'abcde', 'This overflows on C', GETDATE())
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)
- 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.
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.
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:
It seems Microsoft has tried to delete evidence of this bug's true age. Fair enough. Find the old site archived here.
The best solution I have found is by selecting only the structure into a new table (SELECT INTO FROM WHERE 1 = 0) and comparing the columns types and lengths.Full answer here.
Error In SSIS on insert
One of your is getting an input value that is larger (in size) than what the column size is, as defined in your database table.
It's unfortunate that the error message does not also give you the name of the offending column. I think you are stuck with troubleshooting this the hard way, by looking at the values column by column until Microsoft fixes the error message
See Microsoft Connect: Please fix the "String or binary data would be truncated" message to give the column name
...and all the commentary on
Why isn't “String or Binary data would be truncated” a more descriptive error?
identifying values which would be truncated in an insert query
There is no particularly satisfactory way of finding the problematic rows.
This blog post has one solution. (
SET ANSI_WARNINGS OFF and insert into a staging table then use
EXCEPT against the source to find rows that did not import successfully)