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

Development
66 Comments

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.

If you’re on SQL Server 2019+,
here’s how to fix it.

Run this in the database where you want specific truncation warnings, and it takes effect right away:

Another way you could fix it is to upgrade your database’s compatibility level to 2019+ (150+), but that causes a whole bunch of other things to take effect too, like adaptive memory grants, and it’s going to require a whole lot more testing with your code. Just keep it simple for now and run the above if all you’re trying to do is know which column & row is being truncated.

If you’re on SQL Server 2016-2017:
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

66 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
  • Andrew Clennell
    October 25, 2019 3:24 am

    Any way of tracing on server build 12.0.

    Reply
  • Andrew Peterson
    December 31, 2019 1:25 pm

    Awesome!
    Step One: “To fix it, turn on trace flag 460.”
    warning step Two: “Don’t leave this trace flag enabled.”

    Reply
    • Yep, you only need to fix your code once – you shouldn’t have code that continuously throws that error over time. If you do, then the fix involves this:

      Step 1: stop writing code that doesn’t check incoming data lengths

      And great news: there’s no step 2. 😉

      Reply
  • hi, Great

    https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-ver15
    : Starting with database compatibility level 150, message ID 2628 is the default and this trace flag has no effect.

    Thank you,
    Nima

    Reply
  • Thanks! Good and exhaustive info on this. Helps a lot!

    Reply
  • Michael Hudson
    October 20, 2020 3:40 am

    Thanks for the article Brent, very useful. Any idea how to get this new error message into the Flat File Importer that comes with SSMS? I’m using SSMS 18.6 and SQL 2019.

    Reply
  • How to fix the error “String or binary data would be truncated” – can this be fixed without upgrading to sqlserver 2017? i am getting this issue during merge replication

    Reply
  • Thank you Brent. I encountered the error while trying to update a column with it’s value concatenated with another value. The culprit in my case was the trailing white space in the column. Trimming, the column while using the CONCAT(RTRIM(SOME_COL), ‘100’) function would solve it.

    Reply
  • Hi I’m not a computer developer, but have this message, string and binary would be truncated, i was wondering how do I fix it?

    Reply
  • Hi, thank you very much for your post.
    Recently I had the issue with table variable. When I tried to insert rows in the table variable, I got this error.
    I tried the same but with temp table and everything worked fine.
    First I thought the column size was not ok, but column definition had worked for temp table. Then I changed the column size and it had worked also for table variable.
    Any ideas why this happens?
    Thanks!

    Reply
  • Leonardo Hernandez
    July 27, 2021 11:30 pm

    What’s wrong with changing the character length to MAX? Would that cause any problems?

    Reply
    • Yes, it causes problems around index storage and query workspace memory grants. We discuss those in my Mastering Index Tuning and Mastering Query Tuning classes.

      Reply
  • Tim Cartwright
    August 30, 2021 7:59 pm

    Brent like you and many others I am not a fan of turning on trace flags globally unless I’m forced to. So I wrote this trigger that will work pre-150 compatibility mode. Do you know of a way to implement something similar to this with 150 and beyond? One of the great benefits of trace flags is that we could do them per connection or per query. If the per connection option is removed, what do we do when we don’t want to turn that on globally?

    IF EXISTS (SELECT * FROM master.sys.server_triggers WHERE parent_class_desc = ‘SERVER’ AND name = N’truncated_string_trigger’)
    DROP TRIGGER [truncated_string_trigger] ON ALL SERVER
    GO

    CREATE TRIGGER truncated_string_trigger
    ON ALL SERVER
    FOR LOGON
    AS
    — STEP 1 – Alter the LOGIN string here to have this trace flag enabled for connections by this user
    — STEP 2 – Enable the trigger
    — STEP 3 – When done debugging, disable the trigger
    IF ORIGINAL_LOGIN() = ” BEGIN
    DECLARE @msg VARCHAR(100) = CONCAT(‘TURNING ON TRACE FLAG 460 FOR SPID = ‘, @@SPID)
    RAISERROR (@msg, 0, 1) WITH NOWAIT
    DBCC TRACEON(460);
    END
    GO

    — ENABLE TRIGGER [truncated_string_trigger] ON ALL SERVER
    GO
    DISABLE TRIGGER [truncated_string_trigger] ON ALL SERVER
    GO

    Reply
    • Tim Cartwright
      August 30, 2021 8:04 pm

      This work around might work for Michael Hudsons question above about the flat file importer.

      Reply
    • Tim – neat idea! I’m nervous, though. The thought of rapidly flipping trace flags on/off, even just at the logon level, makes my stomach feel the same way I felt when I picked up a javelin for the first time. I mean, I’m probably not going to impale anybody, but…

      This feels like the kind of thing that could go horribly awry if there’s even the slightest problem – for example, if there’s any kind of memory leak involved with flipping trace flags on or off, or if someone’s connection pooling breaks and you start to get hundreds of logins per second.

      If it works for you though, cool – I would just be really, really careful there about monitoring the overhead of this.

      Reply
      • Tim Cartwright
        August 31, 2021 2:37 pm

        Good point, what do you think about adding an xp_logevent call to the trigger? That way the frequency can be monitored. Not sure it solves the problems you describe, but it would help diagnose them if they occur.

        We are being forced to use something like this. We did an Informix -> SQL Server conversion. In informix, the extra characters are just happily eaten. 🙂 So we have these truncated errors firing from quite a few places.

        Reply
        • Tim Cartwright
          August 31, 2021 2:38 pm

          Also, I am not suggesting leaving on the trigger, just for debugging sessions.

          Reply
          • Tim Cartwright
            August 31, 2021 4:31 pm

            So, to make the trigger work you will need a sysadmin user to execute it as.

            – BEWARE: Login triggers can lock down your system if not coded right (Knew that, just learned it the hard way)
            – This login running the trigger should be a certificate account, and not have a password.
            – Can also optionally disable the login. Does not seem to affect trigger execution
            – Don’t leave the trigger enabled.
            – Don’t test modifications on a server that other users are connecting to.

            Here is a working version: (CAVEAT EMPTOR) https://gist.github.com/tcartwright/16d22aceb3dadef283e08d118589461b

  • Aaron Bertrand
    October 28, 2021 7:58 pm

    Due to Microsoft orphaning content again, the code path bug was moved here:

    https://feedback.azure.com/d365community/idea/7f4fc0ab-7e25-ec11-b6e6-000d3a4f0da0

    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