I have a stored procedure that inserts 650 fields into a table. The insert is failing with a truncation error.

It's a simple

SELECT (a bunch of fields) 
FROM (a bunch of tables)

Below is the error message:

Msg 8152, Level 16, State 14, Procedure DSP_Procedure, Line 1075 String or binary data would be truncated.

Is there a quick way that I can identify what field is causing the truncation error?

The fact that the select statement to be inserted into the table has 650 fields makes it difficult to pinpoint which field is causing the truncation error.

I am thinking I can maybe comment out blocks of fields at a time so as to only have the SP insert 100 fields at a time and then run the SP 6 or 7 different times until i can at least narrow down to a group of 100 fields that will contain the field that is causing the truncation error.

Alternatively I am thinking that maybe I can just SELECT INTO a new table and then compare the data lengths in the table vs the data lengths of the target table I am trying to insert into in my SP to see which field contains a longer than expected field length...

I am using SQL Server 2014.

Any easier alternatives?

  • 1
    I would go to INFORMATION_SCHEMA.COLUMNS and compare the data types to the ones you're trying to insert. Unfortunately SQL server does not have dynamic datatypes for variable declaration like ORACLE does.
    – SQLDevDBA
    Jul 10, 2017 at 12:54
  • 2
    I would use your second option, insert into a new (or #temp) table and then compare the column lengths. Or you could wrap LEN() around all the columns in the select and then have an outerquery do a MAX() for each... that would give you the largest text length for the fields. Of course, that assumes it's a char field that is giving you problems. Not using smalldatetime or tinyint? Jul 10, 2017 at 13:00
  • 1
    I'd go with the "Select Into" approach and compare column lengths, yes. Maybe with "WHERE 1=0" so that the table has no rows. Awkward if your SELECT doesn't include unique names for selected columns. I format long column lists as one line of script per column, then "AS" column name on the next line if required, and a blank line after four columns to make it easier to keep place in the list. That also supports selecting many lines and doing Ctrl+K Ctrl+C to change them to comments, so you could attack the Insert operation that way, but the columns left out would have to be nullable. Jul 10, 2017 at 23:01

4 Answers 4


If you are on SQL Server 2016 (SP2, CU6 or newer), one option is to turn on trace flag 460 e.g. (QUERYTRACEON 460). The output will indicate the column and offending data.

See this article for details. https://www.brentozar.com/archive/2019/03/how-to-fix-the-error-string-or-binary-data-would-be-truncated/

If you don't care about the truncation you can use SET ANSI_WARNINGS OFFto ignore that type of truncation.

  • Be aware that this trace flag does not actually work with SQL Server 2016 SP2 as opposed to what is stated in the article. You need at least SQL Server 2016 SP3. Would be nice if Microsoft would actually accurately document this, as the documentation for trace flag 460 states "This trace flag applies to SQL Server 2017 (14.x) CU12 and higher builds."
    – Tom Lint
    Jul 27, 2022 at 15:37

Unfortunately, you have encountered quite old a "feature". There's been a Connect ticket open since 2008, and for almost ten years this hasn't been significant enough to warrant a fix.

Update: For those interested about the history of the issue, the Connect site has long since been retired. The original suggestion can still be viewed via Wayback Machine. As per another answer, use the trace flag to see the colun name.

The standard workaround is, like you figured, a select into... followed by comparing table metadata. Another possibility is binary searching the offending column, but that's manual work too. There are some hacks for metadata comparison, but simple, elegant solution doesn't exist. Maybe some third-party tools would be of help, but I am not aware of such.

  • first link is gone, can you fix it? I am interested
    – WestFarmer
    Jan 26 at 2:31
  • @WestFarmer There's a copy in the Wayback Machine. I'll edit it in.
    – vonPryz
    Jan 26 at 6:56

Using (QUERYTRACEON 460) did not work for me when putting it at the end of my query.

I turned it on at the DB level and it worked:

DBCC TRACEON(460, -1);

But, make sure to turn it back off once you've found and fixed the issue, do not leave it on!


I have opted to copy the list of column names and data size, i.e. k to an Excel spreadsheet. You will likely have to perform some intermediary steps in Excel such as Text to Columns to break down the list to respective columns for Name and Size.

Within Excel generate a formula to compose the WHERE clause such as:

="LEN(" & A1 & ") > " & B1 & " OR "

Drag/copy the formula down the spreadsheet.

To obtain...

LEN(column1) > [SizeColumn1] OR

LEN(column2) > [SizeColumn2] OR

LEN(column3) > [SizeColumn3] OR ...

You can then drag/copy the formula for each of the columns in the list.

Then compose a SELECT on the "will be truncated" data and paste the WHERE from the Excel formula.

You can include in the SELECT a series of CASE statements (also generated in the Excel spreadsheet and copied to the SQL statement) to help identify the offending column size. Such as

="CASE WHEN LEN(" & A1 & ") > " & B1 & " THEN '" & A1 & "' ELSE 'x' END,"

Drag/copy the formula down the spreadsheet

To obtain:

CASE WHEN LEN(column1) > [SizeColumn1] THEN [Column1Name] ELSE 'x' END,
CASE WHEN LEN(column2) > [SizeColumn2] THEN [Column2Name] ELSE 'x' END,
CASE WHEN LEN(column3) > [SizeColumn3] THEN [Column3Name] ELSE 'x' END....

You end up with a SQL statement similar to


CASE WHEN LEN(column1) > [SizeColumn1] THEN [Column1Name] ELSE 'x' END,

CASE WHEN LEN(column2) > [SizeColumn2] THEN [Column2Name] ELSE 'x' END,

CASE WHEN LEN(column3) > [SizeColumn3] THEN [Column3Name] ELSE 'x' END

FROM [data with truncated column]


LEN(column1) > [SizeColumn1] OR

LEN(column2) > [SizeColumn2] OR

LEN(column3) > [SizeColumn3]

Allow Excel drag/copy to generate the respective lines for SELECT and WHERE for the numerous columns

I probably wouldn't perform these steps on a small table. But should you be dealing with a record set with dozens or hundreds of columns this would be a time-saver.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.