SQL SERVER – Identify the column(s) responsible for “String or binary data would be truncated.”

I am happy to write this blog post as I am really happy that finally, SQL Server Engineering team has decided to fix the error which we all have been struggling for many years. I will say Since the year 2000, I have been working with SQL Server and it has always bothered me that every time when I try to insert data into my table which is larger than the data type of the column, I got an annoying error String or binary data would be truncated. I recently demonstrated this to my Comprehensive Database Performance Health Check.

SQL SERVER - Identify the column(s) responsible for "String or binary data would be truncated." stringerror

To be honest, I am very much confident that every single person who has worked with SQL Server will agree that the error related to String or Binary data is the most annoying because it does tell us that error has occurred but will not tell us which column has the problem. This becomes extremely difficult to debug when you face this error doing a mass import of multiple rows with a wide table with multiple columns. I have seen users doing a different workaround to avoid this error as this just breaks your workflow so bad that the entire automation process becomes a manual process.

Let us run a quick test and here is the script for the same.

CREATE TABLE Table1
(Col1 VARCHAR(10), Col2 VARCHAR(10))
GO
INSERT INTO Table1 (Col1, Col2)
VALUES('ElevenChars','Cols')
GO

Let us see how this error occurs in SQL Server 2017.

Msg 8152, Level 16, State 30, Line 4
String or binary data would be truncated.
The statement has been terminated.

Now let see the same error in SQL Server 2019.

Msg 2628, Level 16, State 1, Line 4
String or binary data would be truncated in table
‘tempdb.dbo.Table1’, column ‘Col1’. Truncated value: ‘ElevenChar’.

The statement has been terminated.

You will see that error which has been annoying so far now finally started to make sense. One more reason to upgrade to the latest version in SQL Server.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , , , ,
Previous Post
SQL SERVER – List Query Plan, Cache Size, Text and Execution Count
Next Post
Get-AzureStorageBlob: The Remote Server Returned an Error: (403) Forbidden. HTTP Status Code: 403 – HTTP Error Message: This Request is Not Authorized to Perform This Operation

Related Posts

8 Comments. Leave new

  • Reply
    • Yes it does but not out of the box… we need to enable traceflag 460 after installing the necessary CU’s. In SQL Server 2019 it just works out of the box.

      Reply
  • Mark Gregory
    April 8, 2019 8:59 pm

    Wish we had an option to truncate and insert when a string is too long, ignoring the error. Usually I would not use that option, but there are definitely times where truncation and successful insert would have been highly preferable to stopping the entire import.

    Reply
  • Hello Pinal,

    remember that You can use trace flag 460 for debugging to find in which column and a row is a problem.

    Reply
  • Is there any way for getting exact column names in error messages in SQL server 2012 version.
    Kindly let me know….

    Reply
  • fondationgilbertbruneaudls
    October 31, 2019 12:48 am

    What about this error (String or binary data would be truncated.) when is it thrown by the replication agent?

    Reply
  • This command works for SQL database not for SQL DW

    ALTER DATABASE SCOPED CONFIGURATION
    SET VERBOSE_TRUNCATION_WARNINGS = ON;

    is there an option to identify the column in Synapse

    Reply
  • Hi Team,

    We just enable this flag to Trace column name for Data Truncation.
    But this seems to work for sting type field only.
    Is there any way we can trace out int field having this kind of problem, please sugggest.

    Thanks

    Reply

Leave a Reply

Menu