Intermittent string truncation error

[Guest]

Using SQL 2005

We have a proc that we are calling from an ASP.NET application using
ADO.NET. It is intermittently return the following error message: 'Error
executing the command exec [proc_name] to DataSet. String or binary data
would be truncated. The statement has been terminated.'

If I run the same proc in SQL, it is never returning the error. If I
compile the proc and access my web page again, the query runs fine. After a
length of time (not sure exactly what length or what other factors there
might be), the query returns this error again in the web page.

I am trying to narrow down what the issue might be. Based on the error, it
seems like a fairly straightforward thing to hunt down in SQL, but I am not
getting the error in SQL (EVER!). To be on the safe side, I have reviewed
the code to make sure that there are no places that truncation could happen.

I am wondering if SQL is trying to recompile the proc and if that could
possible cause this error.

Any help or pointers in how to troubleshoot this would be greatly appreciated!

 

[Mike C#]

Generally this is caused when you try to insert or update a value in a
column that is too small to hold the value. For instance, trying to put
'EMMA' into a CHAR(2) column. I would check the column widths for columns
that are updated/inserted in the stored procedure first. There may be a
string concatenation taking place in your client app or in the stored
procedure prior to the insert or update.

 

[Alex Kuznetsov]

Using SQL 2005

We have a proc that we are calling from an ASP.NET application using
ADO.NET. It is intermittently return the following error message: 'Error
executing the command exec [proc_name] to DataSet. String or binary data
would be truncated. The statement has been terminated.'

If I run the same proc in SQL, it is never returning the error. If I
compile the proc and access my web page again, the query runs fine. After a
length of time (not sure exactly what length or what other factors there
might be), the query returns this error again in the web page.

I am trying to narrow down what the issue might be. Based on the error, it
seems like a fairly straightforward thing to hunt down in SQL, but I am not
getting the error in SQL (EVER!). To be on the safe side, I have reviewed
the code to make sure that there are no places that truncation could happen.

I am wondering if SQL is trying to recompile the proc and if that could
possible cause this error.

Any help or pointers in how to troubleshoot this would be greatly appreciated!

did your run Profiler to record which parameters does it fail with?