T-SQL & Development
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.
|
1 2 3 4 5 |
CREATE TABLE dbo.CoolPeople(PersonName VARCHAR(20), PrimaryCar VARCHAR(20)); GO INSERT INTO dbo.CoolPeople(PersonName, PrimaryCar) VALUES ('Baby', '2006 Subaru Impreza WRX GD'); GO |
Baby’s car is longer than 20 characters, so when the insert statement runs, we get an error:
|
1 2 3 |
Msg 8152, Level 16, State 30, Line 5 String or binary data would be truncated. The statement has been terminated. |
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:
|
1 2 |
ALTER DATABASE SCOPED CONFIGURATION SET VERBOSE_TRUNCATION_WARNINGS = ON; |
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:
|
1 2 3 4 |
INSERT INTO dbo.CoolPeople(PersonName, PrimaryCar) VALUES ('Baby', '2006 Subaru Impreza WRX GD') OPTION (QUERYTRACEON 460); GO |
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:
|
1 2 3 4 |
Msg 2628, Level 16, State 1, Line 9 String or binary data would be truncated in table 'StackOverflow2013.dbo.CoolPeople', column 'PrimaryCar'. Truncated value: '2006 Subaru Impreza '. |
You can turn on this trace flag at the query level as shown above, or at the server level:
|
1 2 |
DBCC TRACEON(460, -1); GO |
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:
|
1 2 |
DBCC TRACEOFF(460, -1); GO |
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:
https://twitter.com/dbafromthecold/status/1108366163350679552
Sadly, that behavior is NOT fixed, and here’s the simplest repro script I could build:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CREATE OR ALTER PROC dbo.Repro @BigString VARCHAR(8000) AS BEGIN DECLARE @Table TABLE ( SmallString VARCHAR(128) ) IF ( 1 = 0 ) /* This will never run */ INSERT INTO @Table ( SmallString ) VALUES(@BigString) OPTION (QUERYTRACEON 460) END GO DECLARE @BigString VARCHAR(8000) = REPLICATE('blah',100) EXEC dbo.Repro @BigString GO |
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!
Free, 3× a week
Get my new posts by email
Three posts a week, plus a Monday roundup of the best database news from around the web.
I think you also need to see this from Kenneth Fisher https://sqlstudies.com/2019/03/20/mixed-feelings-over-the-new-truncation-error/
Yeah, I saw that, but I didn’t see that as a big deal. If your users can see raw, unedited error messages, you’re doing security wrong to begin with, heh.
We talking users at the app level or SSMS?
Yes
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.
A-W-E-S-O-M-E, congrats!
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
I appreciate you covering this topic, and the level of detail you provided, too. Many thanks; Another opportunity to learn something new.
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.
I’m from the future and Googling is now Geminiing (yeah not as easy to say and we’re not sure if we like it yet) and you’re about to hit a pandemic but we make it through and somehow I missed this little nugget earlier but thank you for the ancient wisdom!
Brent always finds a different angle when it comes to new features and major changes. That’s why I look in his blog
You are always a great resource of knowledge dude!
Awww, thanks!
Hey Subaru, bring back the WRX hatchback you cowards!
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.
Brad,
I try to remember to add “nullif(” every time I type a “/”. See why at: https://stackoverflow.com/questions/861778/how-to-avoid-the-divide-by-zero-error-in-sql
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.
PS: I just created a UserVoice entry for this bug – https://feedback.azure.com/forums/908035-sql-server/suggestions/37161820-sql-server-2016-sp2-cu6-traceflag-460-does-not-wo
Amazing !
Thanks! This is very helpful. I have been keen to get this properly implemented/
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
Ryan – thanks! Not that I’m aware of, though.
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
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?
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.
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.
Trace Flags are a (not *the*, there are SO many) wild frontier of SQL Server.
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?
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.
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.
Danish – try reading the first sentence of the post next time, thanks.
error
hi,
Please below find attachment
the solution for this is simple, find the column which has this issue. After that change the data type character.
Right, now in a table with 100 columns and an import file with 500,000 rows, how do you “find the column”?
(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 🙂
LOL
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!
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.
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
I think you might need to be on this build 14.0.3076.1 CU14 of SQL 2017
Have installed the latest updates (build 14.0.3223.3) and it worked, but only setting the flag at server level.
When used at query level it still shows the generic message.
Many thanks!
You’re welcome, glad we could help.
Any way of tracing on server build 12.0.
Andrew – sadly, no, as the post explains.
Awesome!
Step One: “To fix it, turn on trace flag 460.”
warning step Two: “Don’t leave this trace flag enabled.”
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. 😉
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
Nima – great feedback, thanks! I’ve updated the post.
Thanks! Good and exhaustive info on this. Helps a lot!
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.
No, sorry.
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
Sravan – read the first sentence in the post. It’s the first sentence. I’m not asking for a lot here.
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.
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?
Sharon – if only there were some kind of article that you could find on the web about this topic, eh?
I guess we’ll never know.
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!
For general Q&A, head to a Q&A site like https://dba.stackexchange.com or https://sqlservercentral.com.
What’s wrong with changing the character length to MAX? Would that cause any problems?
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.
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
This work around might work for Michael Hudsons question above about the flat file importer.
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.
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.
Also, I am not suggesting leaving on the trigger, just for debugging sessions.
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
Due to Microsoft orphaning content again, the code path bug was moved here:
https://feedback.azure.com/d365community/idea/7f4fc0ab-7e25-ec11-b6e6-000d3a4f0da0
How to address this issue “String or binary data would be truncated” in older version on SQL server 2012
Unfortunately, SQL Server 2012 didn’t get the fix because it’s on extended support, which means security fixes only. Even worse, that expires in July 2022 – it’ll be completely unsupported at that point.
This is only tangentially related but I recently encountered an issue with ‘String or binary data would be truncated’ on an insert that actually inserted zero rows on SQL Server 14.0.3045
After experimenting I found that when you do
‘insert into [table1]
select fields
from table2
where not exists (select 1 from table3 where table3.key = table2.key)’
it appears it’ll kick out the truncation error if any of the selected fields from table2 contain a value too long for table1 regardless of whether they’re excluded by the WHERE NOT EXISTS statement or not.
This doesn’t happen when you use a left join and a ‘where table3.key is null’ instead.
I haven’t been able to find any explanation online of why this is and wondered if you knew the reason for that particular quirk.
Your best bet there would be to open a support call with Microsoft.
We have SQL 2019 but DB is in 120 compat level. Will either of these options work safely?
Read the article really slowly – that’s covered.
For SQL 2019 DB in 120 compat mode, we had to enable trace flag 460. Worked great.
Great blog post.
In SQL Server 2019 120 compatibility mode the trace flag on query level was not working even after verbose was on and had to change compatibility level to 150 to check the data.
Why ?
Read the post again, and this time, focus on the section for SQL Server 2019. Cheers!
u saved my day
Glad to hear it!
I have a fixed-length column that is 15 characters wide.
When I got this error on an INSERT of 89K entries that all had a length of 15, I applied LEFT(«column name», 15) and it solved the problem.
If all of the entries were truly 15 characters, then you wouldn’t have gotten the error.
The fact that you got the error – and fixed it by doing the LEFT 15 – tells you that some were longer than 15.
What you should have done was selected the data whose length was greater than 15, which would have let you narrow down the problems, and fixed them rather than truncating the data.
Is there a variation/version we can use to detect this error for numeric truncation like decimal ?
“System.Exception: Error on executing Text ‘[dbo].[ABC_XYZ] @val’ : Numeric arithmetic causes truncation”
Not that I’m aware of.