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 30
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 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