How to fix the error “String or binary data would be truncated”

To fix this error, patch to SQL Server 2016 SP2, CU6 or newer (including SQL Server 2017), and then turn on trace flag 460. You can enable it at the query level or at the server level.

First, let’s see the error happen: let’s create a table with small fields, and then try to insert more data than it holds.

Baby’s car is longer than 20 characters, so when the insert statement runs, we get an error:

String or binary data would be truncated

That sucks because we have no idea which field is causing the problem! It’s especially terrible when you’re trying to insert lots of rows.

To fix it, turn on trace flag 460.

Trace flag 460 was introduced in SQL Server 2016 Service Pack 2, Cumulative Update 6, and in SQL Server 2017. (You can find & download the most recent updates at SQLServerUpdates.com.) You can turn it on at the query level, like this:

And now when the query runs, it shows you which column is getting clipped, and which row, too. In our case, we’ve only got one row – but in your real-life data, you’ll be much happier knowing which row’s triggering the error:

You can turn on this trace flag at the query level as shown above, or at the server level:

That turns it on for everybody, not just you – so get everybody on your team to agree before you turn it on. This changes the error message number from 8152 to 2628 (as shown above), which means if you’ve built error handling based on the error number, you’re suddenly going to start getting different behavior.

I’m a fan of turning this trace flag on while I’m doing troubleshooting, and then once I’ve found the culprit, turning it back off again:

In our case, once we’ve identified that Baby’s car is too large, we either need to change his car, or change the datatype in our table to make it bigger, or cast the data on the way in to explicitly strip the extra length off his car. A data chop shop, if you will.

Don’t leave this trace flag enabled.

There’s at least one bug with it as of today on SQL Server 2017 CU13: table variables will throw errors saying their contents are being truncated even when no data is going into them. Andrew Pruski reports:

Sadly, that behavior is NOT fixed, and here’s the simplest repro script I could build:

SQL Server 2017 CU13 still reports that the string will be truncated even though the insert doesn’t run:

Switch out the table variable for a temp table, and it works fine, as expected:

If you want to follow progress on that bug getting fixed, it’s here. Such a great example of why I’m not a fan of using trace flags by default – sure, they can fix issues, but they can also introduce unpredicted, unwanted behaviors. (And, uh, not a fan of table variables either.)

Update 2019/03/25 – the above bug is fixed in 2017 Cumulative Update 14. Yay!

Previous Post
Registration Open for My SQLSaturday Boston Pre-Con
Next Post
Updated First Responder Kit and Consultant Toolkit for March 2019

38 Comments. Leave new

  • I think you also need to see this from Kenneth Fisher https://sqlstudies.com/2019/03/20/mixed-feelings-over-the-new-truncation-error/

    Reply
  • On first look and based on what you are saying I think it might be good in the Development environment (we are lucky we have that) so that errors don’t move up the chain and eventually Production wouldn’t need it.

    Reply
  • eduardo roedel
    March 20, 2019 8:11 am

    A-W-E-S-O-M-E, congrats!

    Reply
  • How many developer hours have been lost, never to be found again, searching for the ever elusive overflow string, in a vast sea of millions and millions of fields in an input file… And, vendors having to defend their stance that, “No, nothing has changed in the extract…” – almost takes the fun out of it, lol

    Reply
  • Steve Armistead
    March 20, 2019 9:56 am

    I appreciate you covering this topic, and the level of detail you provided, too. Many thanks; Another opportunity to learn something new.

    Reply
    • You’re welcome! This is one of those posts I was excited to write when the fix finally dropped in 2016 – it’s the kind of thing a lot of people will be Googling for over time.

      Reply
  • Brent always finds a different angle when it comes to new features and major changes. That’s why I look in his blog

    Reply
  • You are always a great resource of knowledge dude!

    Reply
  • Hey Subaru, bring back the WRX hatchback you cowards!

    Reply
  • This is great! Thank you. I have spent a lot of time just trying to figure out what column was causing the truncation error. I love that we can get it in error message now. I also agree, my use will be just with the query in question and using the hint there. Not at server level.

    I do not have the latest service pack that has this option but what would be equally cool is if we had the column of the “divide by 0” error. Can track that down I know but it can save time if we get that column info too.

    Reply
  • Thomas Franz
    March 21, 2019 1:27 am

    I just tested your bug-procedure under SQL2016 R2 CU6, it does not return an error here.

    On the other hand it still returns the old error 8152 “String or binary data would be truncated.”, when I comment out the IF 1 = 0 (so that it really inserts into the table variable.

    When I replace the @table_variable by and #temp_table it works correct and shows me the new improved error message.

    Reply
  • Amazing !

    Reply
  • Thanks! This is very helpful. I have been keen to get this properly implemented/

    Reply
  • Another brilliant post Brent. Is there something similar to determine which field is causing arithmetic overflow errors? Sometimes people use different scales and precision for the same fields across different databases

    Reply
  • Nice

    I have some memories of spending hours testing code line by line and comparing schemas from different tables to see where the overflow was

    Reply
  • Hi

    Just tried this on a 2016 Developer instance and I’m still getting the same basic error (no mention of the column). This is using the examples above.

    I’ve had a look at the options available (using intellisense) and querytraceon isn’t listed.

    Anyone any ideas?

    Reply
    • Make sure you’re on 2016 Service Pack 2, Cumulative Update 6 as described in the post, and no, IntelliSense doesn’t recommend trace flags.

      Reply
      • Ah, missed that bit.
        We’re on 2016 SP2 CU 5.

        I’ll get our software bods to install CU6 on here so we can give it a proper run-through.

        Reply
      • Trace Flags are a (not *the*, there are SO many) wild frontier of SQL Server.

        Reply
      • Richard Green
        July 1, 2019 5:59 am

        We’ve just had CU7 installed on our DEV instance but we’re still getting the standard “String or binary data would be truncated.” error. Using SELECT @@ VERISON, I now get:-
        Microsoft SQL Server 2016 (SP2-CU7) (KB4495256) – 13.0.5337.0 (X64) May 16 2019 02:24:21 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: ) (Hypervisor)

        I’ve created the table in a Sandbox database as per your code and run the same insert statement.

        This is the INSERT statement:-
        INSERT INTO CoolPeople(PersonName, PrimaryCar)
        VALUES (‘Baby’, ‘2006 Subaru Impreza WRX GD’)
        OPTION (QUERYTRACEON 460);

        Any ideas?

        Reply
        • Richard Green
          July 1, 2019 6:04 am

          Couldn’t find a way to edit my last post, so I’ve replied.

          If I turn the option on at server level (using DBCC TRACEON(460, -1);), it works.

          Looks like the OPTION isn’t working on our system as yet.

          Reply
  • I know this is fixed it to show proper error in SQL 2019 and they also plan to push it to version like 2017 and 2016.

    Reply
  • raffff data
    June 5, 2019 2:44 am

    error
    hi,
    Please below find attachment

    Reply
  • the solution for this is simple, find the column which has this issue. After that change the data type character.

    Reply
    • Right, now in a table with 100 columns and an import file with 500,000 rows, how do you “find the column”?

      Reply
      • (1) Find the statement that produces the 500,000 rows (2) Temporarily recode it as a SELECT statement using SELECT … INTO myTempTable … (3) have a look at the schema of myTempTable, looking for columns larger than the columns of the intended destination table. I realize this process isn’t suitable for a production environment 🙂

        Reply
  • Hi,
    Does anyone know the impact of turning this trace on globally on a busy server? Or is it insignificant since it’s designed to replace the error message number? Thanks in advance!

    Reply
  • Philippe Cand
    July 30, 2019 2:54 pm

    Hi, Great but per company policy I cannot turn on a trace.
    What I do is very simple but also time saver
    Select
    c1, c2, c3
    Into Ctest
    From someTable
    Then I compare the columns definition and find which one needs to be bigger.

    Reply
  • Not working and i do not know why.
    I tried enabling the flag at query level, as well as at server level, without success.
    The error message still shows: “String or binary data would be truncated. The statement has been terminated.”
    Any ideas?
    I have used the exact examples from the article.
    SQL Version is: Microsoft SQL Server 2017 (RTM) – 14.0.1000.169 (X64)

    Thank you,
    Iana

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}