12

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

It's a simple 

INSERT INTO
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?

Improve this question
3
  • 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?
    – Jonathan Fite
    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.
    – Robert Carnegie
    Jul 10, 2017 at 23:01

3 Answers 3

Reset to default
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.

Improve this answer
1
  • 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
9

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.

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.

Improve this answer
2

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);
GO

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

DBCC TRACEOFF(460, -1);
GO
Improve this answer

Your Answer

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

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