10

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?

  • 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. – MguerraTorres Jul 10 '17 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 '17 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 '17 at 23:01
8

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.

0

To fix it, turn on trace flag 460 (QUERYTRACEON 460) The outupt 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/

P.S. If you don't care about the truncation you can use SET ANSI_WARNINGS OFF to ignore that type of truncation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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