Databases
StackOverflow2013
Postsdbo
POWhy do I get "String or binary data would be truncated" in some cases only?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhy do I get "String or binary data would be truncated" in some cases only?
    text
    copied!<p>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.</p> <pre><code>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 -- &lt;-- 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)) -- &lt;-- 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 </code></pre> <p>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?</p> <p>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.</p> <p>Please notice also, that I have solved this issue already by setting varchar in TargetValues table to size of 3 (corrected the error).</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload