String or binary data would be truncated.

[Luvsql]

I have a stored procedure that I have been editing, deleting etc., but it will no longer run due to this error. It does not say which of the 20 fields on the line that is the issue and have multiple temp tables that it is using. I did work before.

I have spent a few hours trying to figure out the issue. Is there anyway of getting sql to actually tell me what field is being truncated to yield the error? It's like trying to find a needle in a haystack.

 

[gmmastros]

I don't think you can convince sql server to tell you the field that is causing a problem.

My suggestion would be to comment out all but the first field. Then, run the query. If you get an error, it's with the first field. If no error, uncomment the 2nd field and re-run. Continue uncommenting the fields until you get to the one that causes the error.

Good Luck.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Nagrom]

Unfortunatey the stored proc won't tell you which is the offending column. There is an easy way to figure out what is doing it though. Just copy the code from the stored procedure into Query Analyzer and execute the SQL directly (you may have to declare and explicitly set variables if you had any passed into the procedure as parameters). Query analyzer will tell you which line number has the problem (you can actually double click on the error text and it will jump you right to it), which should make the problem a lot easier to find.

 

[vongrunt]

List table definition with information_schema.columns view or sp_help, then compare max. size values agains ones you attempt to put into table with INSERT or UPDATE. This will narrow search a bit.

Or trial-and-error (by commenting out parts of statement until error disappears), your choice

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]