SQL error: String or binary data would be truncated

You are correct in that the exception is due to trying to stuff too much data into a character/binary based field. Running a trace should definitely allow you to see which procedure/statement is throwing the exception if you are capturing the correct events, those you'd want to capture would include:

  1. SQL:BatchStarting
  2. SQL:BatchCompleted
  3. SQL:StmtStarting
  4. SQL:StmtCompleted
  5. RPC:Starting
  6. RPC:Completed
  7. SP:Starting
  8. SP:Completed
  9. SP:StmtStarting
  10. SP:StmtCompleted
  11. Exception

If you know for certain it is a stored procedure that includes the faulty code, you could do away with capturing #'s 1-4. Be sure you capture all associated columns in the trace as well (should be the default if you are running a trace using the Profiler tool). The Exception class will include the actual error in your trace, which should allow you to see the immediate preceding statement within the same SPID that threw the exception. You must include the starting events in addition to the completed events as an exception that occurs will preclude the associated completed events from firing in the trace.

If you can filter your trace to a particular database, application, host name, etc. that will certainly make it easier to debug if you are on a busy server, however if you are on an idle server you may not need to bother with the filtering.

Assuming you are using Sql 2005+, the trace will include a column called 'EventSequence', which is basically an incrementing value ordered by the sequence that events fire. Once you run the trace and capture the output, find the 'Exception' event that fired (if you are using profiler, the row's it will be in Red color), then you should be able to simply find the most recent SP:StmtStarting or SQL:StmtStarting event for the same SPID that occurred before the Exception.

Here is a screen shot of a profile I captured reproducing an event similar to yours:

alt text

You can see the exception line in Red, and the line highlighted is the immediate preceding SP:StmtStarting event that fired prior to the exception for the same SPID. If you want to find what stored procedure this statement is a part of, look for the values in the ObjectName and/or ObjectId columns.


By doing some silly mistakes you will get this error.

if you are trying to insert a string like.

String reqName="Food Non veg /n";

here /n is the culprit.Remove /n from the string to get out of this error.

I hope this will help some one.