Why do I get "String or binary data would be truncated" in some cases only?

Question

I'm troubleshooting a problem of SQL Server error message 8152 "String or binary data would be truncated" in some scenarios only. The following query is similar to that one, that is throwing the error.

CREATE TABLE SourceValues (
    SourceId INT IDENTITY (1,1),
    SourceValue VARCHAR(3)
)
GO
INSERT INTO SourceValues (SourceValue) VALUES ('aaa')
INSERT INTO SourceValues (SourceValue) VALUES ('aab')
INSERT INTO SourceValues (SourceValue) VALUES ('aa')
INSERT INTO SourceValues (SourceValue) VALUES ('ab')
INSERT INTO SourceValues (SourceValue) VALUES ('a')
INSERT INTO SourceValues (SourceValue) VALUES ('b')
GO

PRINT 'NOT WORKING #1'
CREATE TABLE TargetValues (TargetValue VARCHAR(2))
INSERT INTO TargetValues (TargetValue) 
SELECT s1.SourceValue
FROM SourceValues s1, SourceValues s2
WHERE s1.SourceId=s2.SourceId+1 AND s1.SourceValue!='aab'
DROP TABLE TargetValues
GO

PRINT 'NOT WORKING #2'
CREATE TABLE TargetValues (TargetValue VARCHAR(2))
INSERT INTO TargetValues (TargetValue) 
SELECT s1.SourceValue
FROM SourceValues s1, SourceValues s2
WHERE s1.SourceId=s2.SourceId+1 AND s1.SourceValue!='aab'
ORDER BY s1.SourceValue
DROP TABLE TargetValues
GO

PRINT 'WORKING #1'
CREATE TABLE TargetValues (TargetValue VARCHAR(2))
INSERT INTO TargetValues (TargetValue) 
SELECT s1.SourceValue
FROM SourceValues s1, SourceValues s2
WHERE s1.SourceId=s2.SourceId+1 AND s1.SourceValue!='aab'
ORDER BY s2.SourceValue -- <-- using s2 instead of s1 for order
DROP TABLE TargetValues
GO

PRINT 'WORKING #2'
CREATE TABLE TargetValues (TargetId INT IDENTITY (1,1),TargetValue VARCHAR(2)) -- <-- using identity column
INSERT INTO TargetValues (TargetValue) 
SELECT s1.SourceValue 
FROM SourceValues s1, SourceValues s2 
WHERE s1.SourceId=s2.SourceId+1 AND s1.SourceValue!='aab'
DROP TABLE TargetValues
GO

DROP TABLE SourceValues

The problem occured in query 'NOT WORKING 1', the others are some thoughts about solutions. Does anyone know about the differences between the not working queries and working queries?

I tested this on SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2 and came to the same results. But I heard that all queries failed on another SQL Server 2008 R2 instance.

Please notice also, that I have solved this issue already by setting varchar in TargetValues table to size of 3 (corrected the error).

Answer 1

It's when the data would overflow the length of the field eg abc into char(2)

You have aaa in your source data

It can't be switched off or silently truncated

See SQL Server silently truncates varchar's in stored procedures for more then you probably ever wanted to know

Edit:

This is odd behaviour

This still fails with an explicit JOIN and breaks one of the "working" queries

SELECT s1.SourceValue FROM SourceValues s1 JOIN SourceValues s2 On s1.SourceId=s2.SourceId+1 WHERE s1.SourceValue<>'aab' order by s2.SourceValue,s1.SourceValue

I can't find anything on MS Connect about this