INSERT BULK in Profiler trace

04 June,2018 by Jack Vamvas

In a Profiler trace you can see INSERT BULK statements , which  are   a mechanism for interface between SQL Server and a client bulk copy API.For example : .Net SqlBulkCopy & ODBC Bulk Copy methods.  The Profiler trace whill display the INSERT BULK statement but the FROM part , meaning you cannot view the values.

The INSERT BULK statement specifies the target tables \ columns including other meta data information - NULL management , triggers etc
The tricky bit from a troubleshooting per, spective is you cannot view the values in the INSERT BULK statement. The values are transfered in a series of TDS messages after the INSERT BULK statement  incorporating metadata information and the actual data. The data is in a binary format (encoded)

 

But there a few methods you can use to troubleshoot data load failures that utilise the INSERT BULK construct.

1) Use the Profiler trace events Error:Exception and Error:UserMessage or Extended Events  These events can give you some error details when process fails. 
2)  Utilise the Ring Buffer events which can report error messages on Tabular Data Stream (TDS) transfers. TDS refers to a protocol for transfering data from applications to database servers.
3)Review error logs on the client side

 

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on INSERT BULK in Profiler trace


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer