HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

INSERT BULK in SQL Profiler without values

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
withoutinsertsqlbulkprofilervalues

Problem

I started SQL Profiler to catch actions that are doing on server, and among them I found one row where is stated:

SQL BatchStarting: insert bulk table_name 


but there is no values?

How to see what database accept from application?

Solution

I don't believe there's a Profiler trace event that shows this information.

Insert Bulk Background

Behind the scenes, the client performs a bulk load by sending a TDS SQL Batch message containing an INSERT BULK statement which specifies the destination table and columns as well as any bulk load options requested by the client (e.g. "keep NULLs," "check constraints" or "fire triggers").

However, as you've observed, this INSERT BULK statement doesn't contain the values to insert. Instead, these are transmitted by one or more TDS Bulk Load BCP messages that follow the SQL Batch message. The first Bulk Load BCP message starts with metadata describing the shape of the data set to come. Following this metadata comes the data to insert, encoded in an efficient binary format. (With minor exception, the format used is identical to the binary format the server uses to send results to the client.)

Unfortunately, Profiler doesn't provide an event that displays Bulk Load BCP messages. :-(

Reference

Possibly Helpful: A SO answer which provides clues on using Profiler to get a sense for what a bulk insert is touching. (Thanks to @SqlWorldWide for mentioning this answer in a comment.)

Context

StackExchange Database Administrators Q#36604, answer score: 3

Revisions (0)

No revisions yet.