Please fix the "String or binary data would be truncated" message to give the column name - by DWalker

Status : 


Sign in
to vote
ID 339410 Comments
Status Active Workarounds
Type Suggestion Repros 49
Opened 4/22/2008 10:04:22 AM
Duplicates 125347 Access Restriction Public


After all these years, the message

Msg 8152, Level 16, State 6, Procedure <ProcName>, Line 61
String or binary data would be truncated.

is a little outdated.  PLEASE tell us the name of the column that is too short to hold the data.  We can figure it out, eventually, but it is often tedious.  The system knows the answer, so it should tell us.

This would greatly simplify developers' lives.   While you're at it, why not say whether it's STRING data or BINARY data?

This reminds me of a hypothetical error message that says "There's an error somewhere, but we're not going to tell you where it is.  You have to find it yourself."

All information that the system has, which can be given to the programmer, should be given.

Sign in to post a comment.
Posted by DWalker on 7/11/2016 at 7:31 AM
To clarify, Mr. Scott, I meant that the part about Error_Output, and tying the error message in to the Output clause, is something that I don't think is a great idea. I see that the rest of your comment is agreeing that the overall request is good. Thanks.
Posted by DWalker on 7/11/2016 at 7:28 AM
@Peter Schott: Generally, error messages do not propagate into the results of an output clause. What you need, in my opinion, is a Try/Catch block which can easily trap the error message (using T-SQL or managed code) and then your code can do the appropriate processing because the code will have the specific error message.

As the originator of this specific Connect item, I personally don't feel that your suggestion is a good idea.

Also, sheesh, this item and its predecessor item (which I opened in 2005) has had enough trouble getting attention from Microsoft. Please don't try to make it more complex for them to address it!

Also, Microsoft, please keep us informed as to the status (as much as you are allowed to).


David Walker
Posted by Peter A. Schott on 7/6/2016 at 10:41 AM
Regarding the change - which would be better/easier for the developers to implement when giving more information? I'm okay with a new error code or a better message indicating which column(s) have the issue of being too large for the current set. It would be even better if it could be extended in some sort of way to tie in to the OUTPUT clause or something similar - if too large, pipe to Error_Output, otherwise continue as normal. However, the biggest help to us would be to know which column(s) are causing the error. Most of the time we can investigate the incoming data a little better once we know which columns are too big.

I'm not sure how useful the row number would be for most of my purposes. I'm usually in an INSERT ... SELECT type scenario in these cases so row number may not be consistent. However, I could see it being useful for bulkcopy operations.

I guess start with the basics by telling us the column name(s) and if possible make it extensible for OUTPUT clauses, row numbers, and such.
Posted by bborder on 7/6/2016 at 9:21 AM
Enhancing this error message to include the column name would dramatically improve our lives. We replicate Cloud App tables to a local MSSQL instance, and this error is common. We have one replicated table with 534 columns, so you can imagine how difficult it would be to find which column is causing the error. We have developed processes, written scripts, etc. all because the error message does not say the column name. If this enhancement is implemented, I would weep tears of joy! ;)
Posted by DWalker on 6/28/2016 at 9:05 AM
I agree. @David Shiflet [MSFT], a message that says something like "String or binary data would be truncated when inserting into column <column_name>" would be great.

If the error message can provide the record number, that's even better.

Thank you.
Posted by Eric Kravetz on 6/28/2016 at 6:58 AM
This error is basically telling us that some data won't fit in the column, right? We don't need a complex error message telling us how to fix the issue, it could be as simple as this: "Cannot insert 'data' on line # into [column]: string or binary data would be truncated. "

This error makes it very difficult for us to use SQL server to work with data sets that have not been cleaned. I waste so much time on this, and it seems like the engine should know exactly what failed.
Posted by DWalker on 6/17/2016 at 3:18 PM
I am SO GLAD that someone is motivated to make improvements! Anything, anything will be better than what happens now.

Are you limited to either changing the error code, or improving the message for the existing error code? I don't have a strong preference either way. Also, it seems that sometimes there is more than one message line that comes from a SQL operation (I think?). I would be OK with leaving the same error message, and adding a new line that gives the (first) column name in the target table that has a problem.

As for the operations, I am mostly concerned with the INSERT statement, inserting data into a table or an updatable view (or into a synonym, which is the same thing). It doesn't matter whether the source is the VALUES statement or a SELECT statement; the source is some other tale object. (Some variations of the MERGE statement can insert into tables, including the regular MERGE statement. Oh yes, the OUTPUT clause can be used from other statements, but that's just another table source.)

What other operations besides INSERT are you referring to?

If you need to either change the existing message format or add a new message, as I said, I'm OK either way. Maybe some others can chime in here... although this Connect item and its predecessor have been open for MORE THAN TEN YEARS so I'm not sure how many people are actively tracking it; people might just drop by and chime in when they run into the same problem and search the Web; I'm not sure.

As Jocassid says below, another database product gives the column name AND the row number. I wasn't even thinking about the row number -- that would be an extra added bonus.

To summarize, I am glad something is being done. Thank you!
Posted by David Shiflet [MSFT] on 6/17/2016 at 2:53 PM
Update - I've gotten one or two engine developers motivated to make some improvements here. I have one question - should any fix we make introduce a new error code, or use this same error code with a better message? One problem is that there are a variety of object types and operations that can raise this error, so there may not be a single format string that makes sense for every situation.
Posted by Jocassid on 6/16/2016 at 3:53 PM
MySQL/MariaDB has feature this (from the manual). Looks like you get one error per bad row inserted though.

Error: 1406 SQLSTATE: 22001 (ER_DATA_TOO_LONG)
Message: Data too long for column '%s' at row %ld

Anyone know about Oracle, Postgres, DB2, etc?
Posted by DWalker on 5/9/2016 at 2:37 PM
Oh, and please note: There are no workarounds. There is one thing LISTED as a workaround, but it's really a "debugging technique" that is very complex and not always possible to perform (if you don't have control over the environments). Thanks again.
Posted by DWalker on 5/9/2016 at 2:30 PM
Why, THANK YOU, thank you, Mr. Shiflet, if you can fix this, many people will be WAY happy.

I just ran across this TODAY using SQL Server 2014. I originally posted this issue while I was using SQL Server 2005 (and I might have been using SQL Server 2000 when I entered the predecessor Connect item that's listed WAY below, number 125347 which Aaron Bertrand commented on).

This will be great if it gets improved.

If you find that it's not possible to improve the error message, PLEASE come back and put a note in this item. Thanks again.
Posted by David Shiflet [MSFT] on 5/9/2016 at 1:22 PM
i'll take a look at this one.
Posted by Bernard Vander Beken on 4/27/2016 at 5:37 AM
I logged this as a suggested improvement for SQL Azure
Posted by Robert M. Mulder on 4/25/2016 at 6:29 AM
Soo... fix almost incoming then? Right??... ?? Anyone??

ECHOOO... Echooo... echooo, nope nothing
Posted by DWalker on 4/20/2016 at 9:04 AM
You know, I really like Microsoft -- they have done a bunch of great work over the past 20 years.

BUT, the lack of response to this issue really makes me mad.

Remember this?

"Posted by Sara [MSFT] on 4/28/2008 at 6:36 PM


Thanks for your feedback. We're tracking your request to improve this error message for a future release of SQL Server.

-- SQL Server Engine Team"

I presume there have not been any "future release[s] of SQL server" since 2008... Or since the predecessor Connect item #125347.

In 2009 (TWO THOUSAND NINE), Aaron Bertrand said:

"In Connect #125347, Microsoft said, "too late for 2005, we will fix for 2008!" Now, "too late for 2008, we'll fix in vNext!" Please consider fixing this in the next release for real."

Aaron Bertrand should be listened to. Poor, poor response, Microsoft.
Posted by Stoonad on 4/20/2016 at 8:15 AM
Maybe we should rephrase it as a "cloud" issue and someone will look at it...
Posted by Mark Entingh on 4/11/2016 at 4:51 PM
Microsoft, wake up!
Posted by Geoff Infield on 2/24/2016 at 2:51 PM
Maybe a fresh perspective will help. I've been coding since 1989 but am relatively new to SQL Server and after DB2 and Oracle and a LOT of other databases, I've hit this problem and I'm actually more stunned that you've ignored pleas to fix it for nearly a decade than I am that it exists - there are many other shortcomings in SQL Server that have made me bang my head against the desk but it's the complete absence of any interest in FIXING it that makes me run around telling every manager who'll listen to AVOID it.
Posted by DWalker on 2/18/2016 at 7:34 AM
GOOD GRIEF, I wish I knew what the deal was. Anyone who gives a cursory reading to the comments, and who cares about SQL, would certainly think that this issue deserves to be fixed. I think it even meets Raymond Chen's dictum that "every feature or fix starts with a 100 point deficit that it must overcome".

Microsoft seems to not care any more. As I noted before, Microsoft said, in 2008, that they would consider fixing this FOR A FUTURE RELEASE.

The future is apparently not here yet.....

I WISH that Erland Sommarskog or Kalen Delaney or Paul Randal or someone of their stature would add their voices here again. (Aaron Bertrand agreed in 2009 that this was worthy.)
Posted by darielmarlow on 2/17/2016 at 6:20 PM
So... what's the deal with this?
Posted by DWalker on 10/8/2015 at 3:28 PM
Over NINE HUNDRED upvotes...

SEVEN YEARS in the queue....

In 2008, Microsoft said they would consider fixing this "for a future release"...

I have asked a few of the SQL gurus to help get some traction on this, but I can only do so much.    I WISH that one of the well-respected SQL people from the community would start pestering Microsoft, through their own channels, to do something about this. It has caused me a lot of grief in the 7 years since I reported it.
Posted by keith9820 on 10/8/2015 at 12:24 PM
Were I to add up all the hours I have dedicated to debugging this error over my many years as a developer I would surely cry. Microsoft, this seems like a trivial fix for you so why continue to torment us?
Posted by sql jac on 9/9/2015 at 5:54 AM
@Microsoft, this issue is a nightmare, surely if there is overhead you could at the very least provide an debug switch ?
Posted by DW1001 on 8/27/2015 at 2:39 AM
@Microsoft SQL Server Engine Team: could we have your feedback, please?

Thanks in advance.
Posted by DaveBolt on 8/17/2015 at 6:20 AM
@Microsoft - please fix this

@AmirCharania thank you for wiping up (yes, use your imagination) in the mean time :)
Your script works very well. Just a reminder if you are using two different connections, (such as two windows in Management Studio - one for INSERT INTO #TempTable, and another for Amir's script) - to use a ## global temp table, which are shared by all connections, rather than a # temp table which is not seen by the other connection window.
Posted by Murali dhar on 7/27/2015 at 11:43 AM
Any fix for this yet??
Posted by bborder on 7/27/2015 at 7:06 AM

We deal with a 413 column table which occasionally had "truncation" errors when inserting data from a cloud-based app. You WOULD NOT BELIEVE the amount of work for us to figure out which column is causing the truncation error (up to 5-6 person hours per occurrence). PLEASE FIX THIS and provide the name of the failing column. Thanks.
Posted by padigan on 6/18/2015 at 1:56 AM
Hi All

I've been stung by this one many over the years (mostly when maintaining other peoples code of course!! ahem!!) but never really thought of logging it, so glad some did! In all my years on the Microsoft stack virtually every (but not all) error message is about as helpful as this one. The only time I've been surprised is the one where it gives the column values for when a unique index would be violated, which of course is very helpful. They can do it for that error so why do we get terribly unhelpful messages like this one?

As everyone else in this comments section would probably agree - I love SQL server, but come on Microsoft please fix this, you'd make so many people happy! :)
Posted by DWalker on 4/29/2015 at 2:41 PM
Thank you, AmirCharania, for that debugging technique which helps if you have control over the environment. (I wouldn't really call it a "workaround".) Yours seems slightly easier to follow than the other "solution" that was posted earlier (and which is not a solution either).

I ran across this again just today, and I actually created a "semi-permanent" work table (not a temp table) with the source data, and I slightly modified your script to find the three columns (out of 176 columns) that needed updating. (I had to remove tempdb from CTE_Temp, and I renamed the CTEs to CTE_Source and CTE_Target for clarity.)

I also added to the Where clauses to show only Varchar columns.


Of course, none of this SHOULD be necessary. 10 years now....

Microsoft said in 2008 that they would "consider" fixing this in; is there anyone who has enough clout to talk to them? Aaron Bertrand? Kalen Delaney? Erland S? Paul Randal? Kimberly Tripp? Bueller? Anyone?
Posted by AmirCharania on 2/17/2015 at 10:48 AM
Here is a workaround for the developers in the mean time. Load the data that you are trying to INSERT into a Temp table (#) by using SELECT Columns INTO #TempTable. Then run the following script. I know this will not work in "live" environments, but this will work in development when you are trying to perform loads from a know data source.

SELECT C.column_id ,
        ColumnName= ,
        C.max_length ,
        C.user_type_id ,
        C.precision ,
        C.scale ,
        DataTypeName =
FROM sys.columns C
    INNER JOIN sys.types T ON T.user_type_id=C.user_type_id
WHERE OBJECT_ID = OBJECT_ID('YourTableNameGoesHere')
CTE_Temp AS (
SELECT C.column_id ,
        ColumnName= ,
        C.max_length ,
        C.user_type_id ,
        C.precision ,
        C.scale ,
        DataTypeName =
FROM tempdb.sys.columns C
    INNER JOIN tempdb.sys.types T ON T.user_type_id=C.user_type_id
    INNER JOIN tempdb.sys.objects O ON o.object_id=c.object_id
WHERE = 'YourTempTableGoesHere'
    FULL OUTER JOIN CTE_Temp T ON D.ColumnName= T.ColumnName
WHERE ISNULL(D.max_length,0) < ISNULL(T.max_length,999)

Posted by AmirCharania on 2/17/2015 at 10:44 AM
Microsoft, please FIX this error! It is simple to fix and would save developers tons of time. If security is an issue, at least indicate the column_id of the destination table that is causing the error. If you really would like to thrill your developer community then provide ALL the columns that are causing this issue, not just the first column that is encountered. Thanks!
Posted by flowten_cj on 1/25/2015 at 6:14 PM
I'd like to provide a solution here for such issue.

1. Import the file to a new table
2. Use below script to get the column length list. Please make sure to change @table_name to the new table created by step 1.

declare @table_name varchar(200)

set @table_name = 'Intransit_Ocean'

declare @max_length_default int, @column varchar(50), @max_length int, @max_length_string varchar(10)

create table #temp_table (column_name varchar(50), max_length int, max_length_default int)

declare @column_id int, @max_column_id int

select @column_id = min(b.column_id), @max_column_id = max(b.column_id) from sys.tables a, sys.columns b where a.[name] = @table_name and a.object_id = b.object_id --and b.system_type_id in ( 231, 167 )

-- select @column_id, @max_column_id

declare @command varchar(2000)

while(@column_id <= @max_column_id)
    set @column = null

    select @column =, @max_length_default = b.max_length from sys.tables a, sys.columns b where a.[name] = @table_name and a.object_id = b.object_id --and b.system_type_id in ( 231, 167 )
    and b.column_id = @column_id

    --select @column, @max_length_default

    if( @column is not null )

        set @command = 'insert into #temp_table(column_name, max_length, max_length_default) select ''' + @column + ''', max(len(cast([' + @column + ']as varchar(8000)))),' + cast(@max_length_default as varchar(5)) + ' from ' + @table_name + ' where [' + @column + '] is not null'

         --select @command


    set @column_id = @column_id + 1

select * from #temp_table

drop table #temp_table
Posted by Keith Beckman on 1/23/2015 at 8:16 AM
No workaround is possible in a live system. When long data is coming from an end user on a website, it's impossible to recreate the request to even do a binary search to find the bad data. It's been way too long that this utterly useless error message has been haunting us.
Posted by raberana on 11/24/2014 at 9:04 PM
please update this error message to reflect the column affected s
Posted by binseraj on 11/22/2014 at 10:16 AM
Posted by vecsoftuk on 10/21/2014 at 11:33 AM
This utterly useless error messages just wasted hours of my day. Please fix.
Posted by SVeugelers on 7/7/2014 at 5:52 AM
Come on Microsoft. Such a small change and you would make a lot of developers happy.
Don't see any security risk in this, but would be a great help. Especially since Entity Framework makes the queries even harder to debug:

exec sp_executesql N'INSERT INTO [Table] ([Field1] ...) VALUES (1,...) SELECT @p1 int...', @p1=10,....

So please, please add this to SQL Server. I beg you.
Posted by Lionel Couillard on 5/27/2014 at 10:54 PM
Please fix this error message !

With the number of developers fighting with this issue every day it's a waste of time for everyone !

The global productivity of the developers in the world would increase if the message was mentioning which columns are causing the trouble.
Posted by James DBAnderson on 5/1/2014 at 3:11 AM
Come on MS. This is a real pain when dealing with inserts that have many string columns. Can't be that hard to add this in.
Posted by DWalker on 4/15/2014 at 7:22 AM
As mentioned earlier: Quote from July 2005: "It is a bit too late to change error msgs in the Yukon product cycle; we will improve this error msg in the next release."

Wow, it has been a LONG time since I posted this, and even longer since connect item #125347 which was entered in 2005. Almost 10 years! Usability, usability, usability.

Microsoft, please wake up. Thanks.
Posted by Christopher Enengl on 4/15/2014 at 4:05 AM
So did Microsoft do anything after this 6 Years???


Thanks for your feedback. We're tracking your request to improve this error message for a future release of SQL Server.
Now we are going to install SQL Server 2014 - but i have no hope at all that this bug is solved "already"...
Posted by Evan Carroll on 12/17/2013 at 2:21 PM
Please don't fix this. I intend to use it as an example of the product's inadequacies for years to come. I've already monkeywrenched my solution.
Posted by Tomislav Bronzin MVP1 on 8/29/2013 at 12:13 AM
Once again I have received this error and went through tedious process of finding in which column it occurs.
Please give this suggestion a priority!
Posted by bbt2d on 7/3/2013 at 12:41 PM
Took me a day to find this:
Posted by P_m_Durrant on 5/29/2013 at 3:06 AM
knowing Microsoft they will replace it with Object reference not set to an instance of an object error, just to help us developers a bit more
Posted by Robert Heinig II on 5/15/2013 at 12:38 AM
Quote from July 2005: "It is a bit too late to change error msgs in the Yukon product cycle; we will improve this error msg in the next release."

Chuckle. Let's not forget that overall SQL Server is a really great product. It's obvious there are some seriously genius-level brains on the team, let's be happy they are not burdened with usability concerns.
Posted by Mike C1 on 3/15/2013 at 1:30 PM
Out of all the improvements that could be made, this is at the top of the list. Hours & Hours of time trying to track this down every time it happens. Just show the column name, how hard cant that be?
Posted by Sajal Bagchi on 2/1/2013 at 3:05 PM
I completely agree with all. We often get this error while importing a flat file while running an SSIS and since the file has more than 50 columns it is really a tedious work to drill down to correct column. Please give it a priority.
Posted by Jerry Birchler on 1/23/2013 at 2:42 PM
I Agree. My suggestions feel very unreasonable if yours isn't given the most priority. This almost feels like a bug.
Posted by Jamie Thomson on 12/6/2012 at 6:06 AM
This error message crops up often in my SSIS logs where often we're loading data from a 3rd party flat file (i.e. We're not wholly in charge of the data). It would help massively if you could improve this error message.
Posted by k00lb0y on 10/8/2012 at 5:59 AM
Can someone please prioritize this request and ensure this is being addressed. We are living with this error and inability to find the culprit table is a disaster. Always afraid of spending hours on this task during BUSY schedules.

Alternatively, a work around would have been a relief for all these 5 YEARS of time.
Posted by Maximilian Haru Raditya on 8/17/2012 at 2:30 PM
Still no progress???
Posted by xyvyx on 8/17/2012 at 7:37 AM
7 years. It's been at least 7 years that this worthless message has been around yet no fix.

The new Activity Monitor, IntelliSense, the Server Dashboard reports.. how much effort was put into these poorly designed "fluff" features vs. solving real-world problems encountered daily by developers and administrators?
Posted by Chad Dokmanovich on 6/20/2012 at 1:47 PM
The binary workaround does not work well in various situations: 1) setting up test data using SSMS to enter data "directly into the table." or 2) if columns disallows nulls. This work around is still very tedious.

I, too am infuriated by the apparent laziness to provide a betetr error message for such a common development (and sometimes production) error.

This one really needs to be done. bad. real bad.
Posted by JohnMSDEV on 3/15/2012 at 10:14 AM
As a developer programming for the MS platform (and as MSFT stockholder, BTW), this error message, and many other vague error messages are really, really frustrating. These are huge time wasters.

Let me make a business case for MSFT: the more you piss off devs with these difficult to support and debug "features", the less likely they'll stick to making apps for the Microsoft platform. There are already better, competitive alternatives in the open source world.

Face it, the more applications developed by software developers for MS platforms, the better. So make developing for MS, less frustrating, and more robust.

Make MS development products more polished, don't just add more features. Make existing features better, and less frustrating. That should be the priority. So that next time, there won't be apprehension, when considering a new MS project, and perhaps there won't be a flight away from MS development.
Posted by Nicolas BRUSCHINI on 1/5/2012 at 12:05 AM
Please improve this error message. It makes us wasting a lot of time tracking the error when there is a lot of columns !!
Posted by Robert Heinig II on 6/29/2011 at 4:39 AM
@Aaron K: too strong words for connect, the guys reading our effluences here are the good ones, after all.

What they should, however, try to get into the heads of the marketing guys hampering them is that small things like this one do add up immensely where customer satisfaction is concerned, and the customers in this product are not to be taken lightly despite bing 'only' devs - this dev breed often has a lot of weight with both management and users in their companies. And there is a marked trend to a proliferation of such trivialities that make everyday use needlessly frustrating from 2000 to 2005 to 2008 R2, though I'd say this effect is most pronounced in SSRS, slightly less in SSIS, and the core server leaves a positive mood overall (If I took the time to properly document all the flaws I found in the first week after migration I'd be fully bound for well over a month).

@Denis - nope, service crashed with a 0x80040005 Null Pointer Reference Exception, and the proper message deeply hidden in a 64kb stack stace.
Posted by aaron_kempf on 5/15/2011 at 6:18 AM
wow, I think that it is a crime that this was suggested 2 versions ago, and you guys keep postponing. Seriously, can I refer this to the UN for crimes against humanity?
Posted by Denis Valeev on 8/29/2010 at 8:12 AM
(0 row(s) affected)
Posted by tedlong on 8/10/2010 at 9:26 AM
Instead of waiting for the next release, how about just adding this feature to the next service pack.

SELECT 'CommonSense' FROM ms
Posted by AaronBertrand on 8/11/2009 at 4:35 AM
In Connect #125347, Microsoft said, "too late for 2005, we will fix for 2008!" Now, "too late for 2008, we'll fix in vNext!" Please consider fixing this in the next release for real.
Posted by Sara [MSFT] on 4/28/2008 at 6:36 PM

Thanks for your feedback. We're tracking your request to improve this error message for a future release of SQL Server.

-- SQL Server Engine Team