Is there any good way to debug "String or binary data would be truncated?"

The year is 2010.

SQL Server licenses are not cheap.

And yet, this error still does not indicate the row or the column or the value that produced the problem. Hell, it can't even tell you whether it was "string" or "binary" data开发者_StackOverflow.

Am I missing something?

---

A quick-and-dirty way of fixing these is to select the rows into a new physical table like so:

SELECT * INTO dbo.MyNewTable FROM <the rest of the offending query goes here>

...and then compare the schema of this table to the schema of the table into which the INSERT was previously going - and look for the larger column(s).

---

I realize that this is an old one. Here's a small piece of code that I use that helps.

What this does, is returns a table of the max lengths in the table you're trying to select from. You can then compare the field lengths to the max returned for each column and figure out which ones are causing the issue. Then it's just a simple query to clean up the data or exclude it.

DECLARE @col NVARCHAR(50)
DECLARE @sql NVARCHAR(MAX);

CREATE TABLE ##temp (colname nvarchar(50), maxVal int)

DECLARE oloop CURSOR FOR

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'SOURCETABLENAME' AND TABLE_SCHEMA='dbo'

OPEN oLoop 

   FETCH NEXT FROM oloop INTO @col;

   WHILE (@@FETCH_STATUS = 0)
   BEGIN
      SET @sql = '
   DECLARE @val INT;
   SELECT @val = MAX(LEN(' + @col + ')) FROM dbo.SOURCETABLENAME;
   INSERT INTO ##temp
           ( colname, maxVal )
   VALUES  ( N''' + @col + ''', -- colname - nvarchar(50)
             @val  -- maxVal - int
             )';
      EXEC(@sql);
      FETCH NEXT FROM oloop INTO @col;
   END

   CLOSE oloop;
   DEALLOCATE oloop

   SELECT * FROM ##temp
   DROP TABLE ##temp;

---

Another way here is to use binary search.

Comment half of the columns in your code and try again. If the error persists, comment out another half of that half and try again. You will narrow down your search to just two columns in the end.

---

You could check the length of each inserted value with an if condition, and if the value needs more width than the current column width, truncate the value and throw a custom error.

That should work if you just need to identify which is the field causing the problem. I don't know if there's any better way to do this though.

---

Recommend you vote for the enhancement request on Microsoft's site. It's been active for 6 years now so who knows if Microsoft will ever do anything about it, but at least you can be a squeaky wheel: Microsoft Connect

---

For string truncation, I came up with the following solution to find the max lengths of all of the columns:

1) Select all of the data to a temporary table (supply column names where needed), e.g.

SELECT  col1
       ,col2
       ,col3_4 = col3 + '-' + col4
INTO   #temp;

2) Run the following SQL Statement in the same connection (adjust the temporary table name if needed):

DECLARE @table  VARCHAR(MAX) = '#temp';      -- change this to your temp table name

DECLARE @select VARCHAR(MAX) = '';
DECLARE @prefix VARCHAR(256) = 'MAX(LEN(';
DECLARE @suffix VARCHAR(256) = ')) AS max_';
DECLARE @nl     CHAR(2)      = CHAR(13) + CHAR(10);

SELECT  @select = @select + @prefix + name + @suffix + name + @nl + ','
FROM    tempdb.sys.columns 
WHERE   object_id = object_id('tempdb..' + @table);

SELECT  @select = 'SELECT ' + @select + '0' + @nl + 'FROM ' + @table

EXEC(@select);

It will return a result set with the column names prefixed with 'max_' and show the max length of each column.

Once you identify the faulty column you can run other select statements to find extra long rows and adjust your code/data as needed.

---

I can't think of a good way really.
I once spent a lot of time debugging a very informative "Division by zero" message.

Usually you comment out various pieces of output code to find the one causing problems.
Then you take this piece you found and make it return a value that indicates there's a problem instead of the actual value (in your case, should be replacing the string output with the len(of the output)). Then manually compare to the lenght of the column you're inserting it into.

---

from the line number in the error message, you should be able to identify the insert query that is causing the error. modify that into a select query to include AND LEN(your_expression_or_column_here) > CONSTANT_COL_INT_LEN for the string various columns in your query. look at the output and it will give your the bad rows.

---

Technically, there isn't a row to point to because SQL didn't write the data to the table. I typically just capture the trace, run it Query Analyzer (unless the problem is already obvious from the trace, which it may be in this case), and quickly debug from there with the ages old "modify my UPDATE to a SELECT" method. Doesn't it really just break down to one of two things:

a) Your column definition is wrong, and the width needs to be changed b) Your column definition is right, and the app needs to be more defensive

?

---

The best thing that worked for me was to put the rows first into a temporary table using select .... into #temptable Then I took the max length of each column in that temp table. eg. select max(len(jobid)) as Jobid, .... and then compared that to the source table field definition.