ansaurus

Question

Is there any good way to debug "String or binary data would be truncated?"

Answer 1

+1 A:

You could check the length of each inserted value with an if condition, and if the value needs more width than the current column width, truncate the value and throw a custom error.

That should work if you just need to identify which is the field causing the problem. I don't know if there's any better way to do this though.

Raze2dust 2010-07-14 17:27:57

Right, of course that's the method I end up using. My question is that, why doesn't SQL Server just tell you where it is *when it finds the problem?*

harpo 2010-07-14 17:40:31

Answer 2

A:

I can't think of a good way really.
I once spent a lot of time debugging a very informative "Division by zero" message.

Usually you comment out various pieces of output code to find the one causing problems.
Then you take this piece you found and make it return a value that indicates there's a problem instead of the actual value (in your case, should be replacing the string output with the len(of the output)). Then manually compare to the lenght of the column you're inserting it into.

GSerg 2010-07-14 17:29:26

Answer 3

A:

from the line number in the error message, you should be able to identify the insert query that is causing the error. modify that into a select query to include AND LEN(your_expression_or_column_here) > CONSTANT_COL_INT_LEN for the string various columns in your query. look at the output and it will give your the bad rows.

KM 2010-07-14 17:33:47

Yes, I'm talking about when you know the query. And this is the kind of manual debugging that I'm trying to avoid. There could be many columns in the table, and there could be more than one causing the problem. Surely what you're describing the computer's job, right?

harpo 2010-07-14 17:39:40

tell the computer how to handle the exception. prevent it in the WHERE, by throwing out the rows, truncate them in your INSERT by doing `RIGHT(....,max_col_len_int)` and never get this error, just truncate it. if you want the progam to handle it, use an IF EXISTS query before the INSERT to find any that will truncate. If you layout your tables with the proper data types this doesn't happen. if you are loading external data, you need to code to handle these exceptions, **SQL SERVER can't read your mind and do what YOU want, it does what the TSQL says.**

KM 2010-07-14 17:50:12

Yes, I have resorted to that method quite often (although I use LEFT of course). But that only works for "string" fields (which, granted, are usually the culprit). What I'm trying to avoid is manually eliminating each field, looking up the width of the target, etc etc. Seems that SQL Server could save us a lot of time by printing information that it presumably already has.

harpo 2010-07-14 17:56:22

I'm not asking for magic, and I understand that I have to write the statement defensively. But is it really necessary for the message to be so vague?

harpo 2010-07-14 18:05:31

Answer 4

+3 A:

A quick-and-dirty way of fixing these is to select the rows into a new physical table like so:

SELECT * INTO dbo.MyNewTable FROM <the rest of the offending query goes here>

...and then compare the schema of this table to the schema of the table into which the INSERT was previously going - and look for the larger column(s).

Will A 2010-07-14 17:41:30

This is the best idea I've heard yet.

harpo 2010-07-14 17:59:50

Answer 5

A:

Technically, there isn't a row to point to because SQL didn't write the data to the table. I typically just capture the trace, run it Query Analyzer (unless the problem is already obvious from the trace, which it may be in this case), and quickly debug from there with the ages old "modify my UPDATE to a SELECT" method. Doesn't it really just break down to one of two things:

a) Your column definition is wrong, and the width needs to be changed b) Your column definition is right, and the app needs to be more defensive

?

ThatSteveGuy 2010-07-14 17:59:40

ansaurus

tags:

views:

answers:

Is there any good way to debug "String or binary data would be truncated?"

related questions