Scuffling with ‘String or binary data would be truncated’

The error ‘String or binary data would be truncated’ can be annoying.  It occurs when you try to insert or update a string or binary column with a value that is too large. Recently I was trying to INSERT from a SELECT from one table to another and I got this error. It can be a pain tracking down the cause, especially if there are a large number of columns or a large dataset involved.

In the past I’ve written queries to give me the LEN for each column, but again if there are a large number of columns involved this can be very time consuming.

Below is a way of identifying which rows are causing the problem. This doesn’t help if you’ve got a large number of columns, as you still need to work out which field is causing the problem, but it will help if you have a large dataset and the problem rows are very sparse.

For this example I’ll create a couple of tables and generate some data. The source table has a column of VARCHAR(50), whereas the destination has VARCHAR(25):

CREATE TABLE SourceTable

    (

    RowId  INT

   ,Chars  INT

   ,String VARCHAR(50)

    )

GO

 

CREATE TABLE DestinationTable

    (

    RowId  INT

   ,Chars  INT

   ,String VARCHAR(25)

    )

GO

Next the tables are populated with a random number of ‘X’s, between 0 and 50. In theory you should get about 50% with a length above 25 characters and 50% below.

DECLARE @i INT

DECLARE @RandomNumber INT

 

SET @i=0

WHILE @i <= 50

BEGIN

    SET @RandomNumber = ROUND(50 * RAND(), 0)

 

    INSERT INTO SourceTable

    SELECT @i, @RandomNumber, REPLICATE('X', @RandomNumber)

 

    SET @i=@i+1

END

GO

Next try inserting from SourceTable to DestinationTable:

INSERT INTO DestinationTable

SELECT * FROM SourceTable
GO

This results in the error:

Msg 8152, Level 16, State 14, Line 1

String or binary data would be truncated.

The statement has been terminated.

It’s possible to ignore the 'String or binary data would be truncated' message by setting ANSI_WARNINGS to OFF. This will truncate fields where they don’t fit. ANSI_WARNINGS OFF has drawbacks and it is better to correct a problem rather than ignore it.

The following can be used to work out which rows are causing the issue:

1. Take a copy of the destination table:

SELECT * INTO #Destination FROM DestinationTable WHERE 1=2

GO

2. Set ANSI_WARNINGS OFF and perform the insert into the copy of the destination table, then set ANSI_WARNINGS ON again:

SET ANSI_WARNINGS OFF

GO

 

INSERT INTO #Destination

SELECT * FROM SourceTable

GO

SET ANSI_WARNINGS ON

GO

As ANSI_WARNINGS is off SQL Server truncates the fields rather than produces the warning.

3. Next compare what you would like to insert against what was inserted with the ANSI_WARNINGS OFF truncating. By using EXCEPT you only select the rows that don't match, and have therefore been truncated:

SELECT * FROM SourceTable

EXCEPT

SELECT * FROM #Destination

GO

The rows that have been truncated and are the cause of the ‘String or binary data would be truncated’ error.

(Note - The use of EXCEPT limits this to 2005/2008. The finaly query could be re-written for SQL Server 2000 and below.)

This isn’t the most elegant solution, and as I said if there were a large number of columns you’d still need to hunt through for the offender(s), but at least this gives an idea of where to look. I may have missed some glaringly obvious solution to this problem, so I’d be interested to know if anyone has any other ways of dealing it.

 

 

 

Published 12 January 2008 07:27 PM by Danny

Comments

# DamianMulvena said on 14 January, 2008 10:31 AM

I love how you have to say "ANSI_WARNINGS OFF has drawbacks and it is better to correct a problem rather than ignore it."! I would have thought that was a given.

I worked on an application recently that stored the month as a single letter. Nobody picked up on the errors so we had a value range of J,F,M,A,S,O,N,D.

# AdamMachanic said on 14 January, 2008 04:12 PM

The month as a single character story is great!!  Unbelievable, yet oh so obviously (and sadly) true at the same time.  Thanks for sharing...

# Danny said on 14 January, 2008 06:38 PM

Ok, ok, apologies for stating the obvious!

# Chris Love's Official ASP.NET Blog said on 26 October, 2008 09:16 PM

I am sure just about any of us that have done any development work against a database, and who hasn’t,

# String or Binary Data Would Be Truncated Error | Yslava Answers said on 10 December, 2014 06:50 PM

Pingback from  String or Binary Data Would Be Truncated Error | Yslava Answers