String or binary data would be truncated -- Heisenberg problem_问答_开发者

When you get this error, the first thing you ask is, which column? Unfortunately, SQL Server is no help here. So you start doing trial and error. Well, right now I have a statement like:

INSERT tbl (A, B, C, D, E, F, G)
SELECT A, B * 2, C, D, E, q.F, G
  FROM tbl
      ,othertable q
 WHERE etc etc

Note that

Some values are modified or linked in from another table, but most values are coming from the original table, so they can't really cause truncation going back to t开发者_Go百科he same field (that I know of).
Eliminating fields one at a time eventually makes the error go away, if I do it cumulatively, but — and here's the kicker — it doesn't matter which fields I eliminate. It's as if SQL Server is objecting to the total length of the row, which I doubt, since there are only about 40 fields in all, and nothing large.

Anyone ever seen this before?

Thanks.

UPDATE: I have also done "horizontal" testing, by filtering out the SELECT, with much the same result. In other words, if I say

WHERE id BETWEEN 1 AND 100: Error
WHERE id BETWEEN 1 AND 50: No error
WHERE id BETWEEN 50 AND 100: No error

I tried many combinations, and it cannot be limited to a single row.

Although the table had no keys, constraints, indexes, or triggers, it did have statistics, and therein lay the problem. I killed all the table's stats using this script

http://sqlqueryarchive.blogspot.com/2007/04/drop-all-statistics-2005.html

And voila, the INSERT was back to running fine. Why are the statistics causing this error? I don't know, but that's another problem...

UPDATE: This error came back even with the stats deleted. Because I was convinced that the message itself was inaccurate (there is no evidence of truncation), I went with this solution instead:

SET ANSI_WARNINGS OFF
INSERT ...
SET ANSI_WARNINGS ON

Okay, it's more of a hack than a solution, but it allows me — and hopefully someone else — to move on to other things.

Is there a reason you can't simply cast the fields as the structural equivalent of their destination column like so:

Select Cast(A as varchar(42))
, Cast(B * 2 as Decimal(18,4))
, Cast(C As varchar(10))
...
From Table

The downside to this approach is that it will truncate the text values at their character limit. However, if you are "sure" that this shouldn't happen, then no harm will come.

In some cases you can run into a problem if you have any other column with default values which might cause the problem.

Ex. you might have added a column to trace the user who created the row, like USER_ENTERED with default value of suser_sname() but the column length is less than the current username.

There is a maximum row size limit in SQL Server 2005. See here.

Most of the time you'll run into this w/lots of nvarchar columns.

Yes, when I ran into this, I had to create another table/tables which mimic the current structure. I then did not change the code, but changed my data type sizes to all nvarchar (MAX) for each field till it stopped, then eliminated them one by one. Yes long and dragged out but I had major issues trying anything else. Once I tried a bunch of stuff that was causing too much of a headache I just decided to take the "Cave Man" Approach as we laughed about it later.

Also I have seen a similar issue with FKs, where you must ask:

What are the foriegn key constraints? Are there any?

Since there are not any , try this guy's DataMgr component:

http://www.bryantwebconsulting.com/blog/index.cfm/2005/11/21/truncated

Also check this out:

http://forums.databasejournal.com/showthread.php?t=41969

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138456

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97349

You could also dump the table you are selecting from to a temp table and find out what line gives the error if it errors out to a temp table.

It should tell you the line in the error, if you put each column on another line, it should tell you exactly where it is bombing.

If it looks like "total length" then do you have audit trigger concatenating the columns for logging?

Edit: after your update, I really would consider the fact you have a trigger causing this...