May 26, 2011 at 8:11 am
Hi,
Has anyone got a way to identify which column is to blame when encountering error 'String or binary data would be truncated'?
I've come accross this when attempting to insert a row into a table with over 75 columns and was wondering if it is possible to glean this info out of SQL server somehow.
Thanks for any feedback,
G
May 26, 2011 at 8:14 am
I use a binary search to do this...
comment out half the lines and see if the error goes away. Keep commenting half the columns untill you target the problem section.
The other way to find this out is to wrap all the columns with MAX(LEN(column)) and then checking 1 by 1 to see which one(s) bust the limits.
May 26, 2011 at 8:30 am
I use a binary search to do this...
Phil the pedant steps in 🙂
What you have described is a 'bisection method' - a binary search acts on a sorted array.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 26, 2011 at 8:32 am
Thanks for the info.
Still works great tho :-P.
May 26, 2011 at 10:44 am
Ninja's_RGR'us (5/26/2011)
The other way to find this out is to wrap all the columns with MAX(LEN(column)) and then checking 1 by 1 to see which one(s) bust the limits.
I use a couple of different approaches based on what rights I have and what environment I'm using.
In a production environment where I only have select writes, I will use a variation of this approach, but instead of checking 1 by 1, I'll paste the results into Excel and also paste the corresponding maximum_character_length data from the Information_Schema.Columns system view for that table. Then I use Excel's conditional formatting to highlight the problem columns. This has the advantage that you will find ALL problem columns rather than just the FIRST problem column.
The way that I format my queries, it's usually fairly easy to use the regular expression features in search and replace to quickly replace Column/Expression with Max(Len(Column/Expression)) except for subqueries.
If the number of records is small enough, I will just copy the data into Excel and use an array function in Excel to calculate the Max(Len()) of each column, since that is usually quicker than reformatting the query to return that info.
In a development environment, I sometimes use INTO <Table> to temporarily create a table so that I can do a direct comparison of the values for that table and the desired destination table, again using Information_Schema.Columns.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 26, 2011 at 2:43 pm
This is one of the most ridiculous things about sql insert errors. Somewhere there was a link to a requested fix but I can't find it just now. How dumb that they can't say which field and if it is string OR binary since it i obvious that at the time the error is thrown by the engine it knows which column. This would of course provide a reasonable way to pass back the column name and what datatype it is. :rolleyes: Be that as it is for now I am glad to find some ways of finding these.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 26, 2011 at 2:53 pm
Ya but all of them are pretty hugle hacks... and you better hope that the query doesn't take 12 hours before failing!
May 26, 2011 at 3:01 pm
Phil Parkin (5/26/2011)
I use a binary search to do this...
Phil the pedant steps in 🙂
What you have described is a 'bisection method' - a binary search acts on a sorted array.
Actually this seems a perfectly legitimate example of exactly what a binary search does. It takes a list of sorted items and determines which half the item you are looking for is in. In this case, is it in the top half (or left half if columns) to determine where the culprit is. The same algorithm applies. Either the columns (or the insert statement) can be bifurcated repeatedly until the offender is identified. Mathematically it is still the same log2N. 😀
The traditional meaning is in fact used on a sorted array but that is essentially what columns are.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 26, 2011 at 3:03 pm
We're so geeks to fight over this!
May 27, 2011 at 1:00 am
Thanks for your sharing your experience guys.
I was getting the feeling that I was somehow missing something obvious in this.
May 27, 2011 at 1:45 am
Sean Lange (5/26/2011)
Phil Parkin (5/26/2011)
I use a binary search to do this...
Phil the pedant steps in 🙂
What you have described is a 'bisection method' - a binary search acts on a sorted array.
Actually this seems a perfectly legitimate example of exactly what a binary search does. It takes a list of sorted items and determines which half the item you are looking for is in. In this case, is it in the top half (or left half if columns) to determine where the culprit is. The same algorithm applies. Either the columns (or the insert statement) can be bifurcated repeatedly until the offender is identified. Mathematically it is still the same log2N. 😀
The traditional meaning is in fact used on a sorted array but that is essentially what columns are.
I was prepared to go along all with of this, indeed with added kudos in respect of getting 'bifurcated' into a SQL discussion, but then I got to the last sentence, which is similar to saying:
"These apples are sorted in order of size, therefore I can do a binary search to find which of them is rotten."
The solution to the original problem has no concept of sort order - Order By NewId() would neither speed up nor slow down its solution.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 27, 2011 at 7:27 am
Phil Parkin (5/27/2011)
Sean Lange (5/26/2011)
Phil Parkin (5/26/2011)
I use a binary search to do this...
Phil the pedant steps in 🙂
What you have described is a 'bisection method' - a binary search acts on a sorted array.
Actually this seems a perfectly legitimate example of exactly what a binary search does. It takes a list of sorted items and determines which half the item you are looking for is in. In this case, is it in the top half (or left half if columns) to determine where the culprit is. The same algorithm applies. Either the columns (or the insert statement) can be bifurcated repeatedly until the offender is identified. Mathematically it is still the same log2N. 😀
The traditional meaning is in fact used on a sorted array but that is essentially what columns are.
I was prepared to go along all with of this, indeed with added kudos in respect of getting 'bifurcated' into a SQL discussion, but then I got to the last sentence, which is similar to saying:
"These apples are sorted in order of size, therefore I can do a binary search to find which of them is rotten."
The solution to the original problem has no concept of sort order - Order By NewId() would neither speed up nor slow down its solution.
So you are saying I should have quit while I was ahead? Bummer. I have a habit of over making my point to the point I invalidate my own argument more often than not.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 27, 2011 at 7:30 am
Sean, no argument there :hehe:;-)!
December 1, 2011 at 9:55 am
Bah!
Msg 8152, Level 16, State 2, Line 91
String or binary data would be truncated.
The statement has been terminated.
Since the engine was trying to run the program, the least it could do when it came across something that would be truncated is to give me the data, and the program line, and the table column where it was trying to do the insert.
I've got I don't know how many tables and how many columns in all the tables and the code is large.
Having a computer to help do trivial compare tasks is one their main jobs in our society. So the least it could do is tell me the details when it hits this type of error.
December 2, 2011 at 4:18 am
Ninja's_RGR'us (5/26/2011)
We're so geeks to fight over this!
Couldn't resist 😀
The bisection method in mathematics is a root-finding method which repeatedly bisects an interval and then selects a subinterval in which a root must lie for further processing. It is a very simple and robust method, but it is also relatively slow. Because of this, it is often used to obtain a rough approximation to a solution which is then used as a starting point for more rapidly converging methods.[1] The method is also called the binary search method[2] and is similar to the computer science Binary Search, where the range of possible solutions is halved each iteration.
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply