String or binary data would be truncated -- Heisenberg problem

Question

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

Answer 1

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

Edit 2, after seeing your statistics answer...

Because the total stats attribute length was probably greater then 900 bytes? Not sure if this applies to statistics though and I'm not convinced.

Do you have a reference please because I'd like to know why stats would truncate when they are simply binary histograms (IIRC)

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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.