10

I'm doing an integration on a community platform called Telligent. I'm using a 3rd-party add-on called BlogML to import blog posts from an XML file (in BlogML format) into my local Telligent site. The Telligent platform comes with many classes in their SDK so that I can programmatically add content, such as blog posts. E.g.

myWeblogService.AddPost(myNewPostObject);

The BlogML app I'm using essentially parses the XML and creates blog post objects then adds them to the site using code like the above sample line. After about 40 post imports I get a SQL error:

Exception Details: System.Data.SqlClient.SqlException:
String or binary data would be truncated.
The statement has been terminated.

I believe this error means that I'm trying to insert too much data into a db field that has a max size limit. Unfortunately, I cannot tell which field this is an issue for. I ran the SQL Server Profiler while doing the import but I cannot seem to see what stored procedure the error is occurring on. Is there another way to use the profiler or another tool to see exactly what stored procedure and even what field the error is being caused by? Are there any other tips to get more information about where specifically to look?

Oh the joys of 3rd-party tools...

APC
  • 144,005
  • 19
  • 170
  • 281
Mark Ursino
  • 31,209
  • 11
  • 51
  • 83
  • 1
    When the error happens, does it undo all of the blog posts already imported? If not, maybe you can tell which record is failing (the next one after the last successful one) and look there. If the import tool has any kind of logging or a counter of records imported then that would be another place to look. – Tom H Dec 11 '09 at 15:40
  • @Tom H. The post successfully go in. I have done what you said in terms of looking at the post after the last successful one. I did suspect that the post body was too large for its db cell but the body field is `ntext` and I didn't see any other suspiciously long XML nodes that were longer than I would expect. I'll take another look at it again as this is currently my only known method to debug this issue. Thanks for the comment. – Mark Ursino Dec 11 '09 at 15:43

2 Answers2

19

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.

Glorfindel
  • 21,988
  • 13
  • 81
  • 109
boydc7
  • 4,593
  • 20
  • 17
  • 3
    Incredible. I spent about 4 days trying to fix this issue. I added the additional events like you said and found the correct SP statement that was failing. I found that `Subject` and `Author` fields that were too short and just successfully imported 700+ blog posts. If I could, I would give you all of my rep points. – Mark Ursino Dec 11 '09 at 19:21
  • Additional tip: Open TWO windows. One with just the Exception view, and one like this. Then you can see exactly how often the exception is happening (or wait for it to occur) at a glance. Then based upon the time of the transaction you can switch to your other view to find the query – Simon_Weaver Nov 25 '14 at 21:43
  • Loving this tip. Still relevant and useful in 2018 :) – Stu1986C Jan 18 '18 at 16:36
1

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.

Raman B
  • 331
  • 4
  • 5