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

Question

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.

Am I missing something?

Answer 1

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).

Answer 2

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;