13

I am trying to find the difference between using SqlBulkCopy with the SqlBulkCopyOptions.UseInternalTransaction copy option and without it, but in my test application I do not detect any difference. If BatchSize is for example 0 and I add 100 records (in a DataTable) where record number 50 causes an error when adding it to the database table, I get 0 records in the table. If BatchSize is set to 10 for example, I get 40 records (4 batches of 10 records, fifth batch includes the faulty record and causes the bulk copy to abort). It does not matter if SqlBulkCopyOptions.UseInternalTransaction is set or not, I always get the same result. It seems like batches are always copied in an internal transaction.

If you're interested in my test application, here it is: SqlBulkCopy-Error-and-Transaction-Test.zip

My questions are:

  1. Is SqlBulkCopyOptions.UseInternalTransaction obsolete because SqlBulkCopy always uses internal transactions?
  2. If not: What's the actual meaning of this option? In which cases would it make a difference?

Hope someone can clarify

Edit: According to the answer and the comments I assume that my problem ist not clear enough. I know the documentation. It says that "By default, a bulk copy operation is its own transaction." and that each batch uses it's own transaction when passing UseInternalTransaction. But if that means that by default the bulk copy operation uses only one transaction for the whole bulk copy (and not one for each batch) I would not get records in the database if I set BatchSize to a certain size and a batch that lies after the first one causes an error. If only one transaction would be used, all records added to the transaction log would be rolled back. But I get the records of the batches that lie before the batch that includes the faulty record. According to this it seems as if by default each batch is executed in it's own transaction. That means that it makes no difference whether I pass UseInternalTransaction or not. If I am on the wrong path here I would really appreciate if someone could clarify.

One fact could be important: I use the SQL Server 2012. Maybe the SQL Server 2008 behaves different. I'll check that.

Edit: Thanks to the reply from usr I think I found the answer: I debugged and profiled a bit and found out that the private field _internalTransaction is really not set if UseInternalTransaction is not defined. SqlBulkCopy then does not use an own (internal) transaction. But profiling indicated that SqlBulkCopy uses TDS (Tabular Data Stream) for copying the data (no matter what BatchSize is). I did not find much information about TDS especially for the SQL Server but I assume that the SQL Server executes TDS bulk copy operations in an internal transaction. Therefore UseInternalTransaction seems to be kind of redundant for the SQL Server, but to be on the safe side I would set it.

Jürgen Bayer
  • 2,993
  • 3
  • 26
  • 51
  • http://msdn.microsoft.com/en-us/library/tchktcdk%28v=vs.80%29.aspx – Tim Schmelter Apr 13 '12 at 07:36
  • Thanks Tim. Yes, the documentation says "By default, a bulk copy operation is its own transaction". A bit later it says "You can explicitly specify the UseInternalTransaction option in the SqlBulkCopy class constructor to explicitly cause a bulk copy operation to execute in its own transaction, causing each batch of the bulk copy operation to execute within a separate transaction". This is confusing. And my test application shows not a single difference! See my comment to the answer from Arion. Only if I pass a own transaction, I get 0 records added in case of an error. Try my test app ... – Jürgen Bayer Apr 13 '12 at 12:49
  • Tim Schmelter: Link is irrelevant. It doesn't clarify the issue of how to write **a batch** to the server without a transaction. If I understand OP's question, it isn't about how to make the entire bulk copy operation a transaction. I confirm Jurgen's finding. UseInternalTransaction is not effective in this case. – JohnC Feb 08 '15 at 11:47
  • So I've just checked this out using SQL Profiler and as far as I can see the difference between using UseInternalTransaction and not using it is this: If you don't specify it the BulkCopy uses the default autocommit transactions mode (i.e. no BEGIN TRAN / COMMIT TRAN statements), if you use UseInternalTransactions then there are explicit BEGIN TRAN / COMMIT TRAN around the BulkCopy statement. – Steve Ford Feb 17 '15 at 09:58
  • I should have added that in reality this means that they are effectively the same. – Steve Ford Feb 17 '15 at 10:30

2 Answers2

6

If you set this option then the SQLBulkCopy class will add a

_internalTransaction = _connection.BeginTransaction();

around each batch.

But this option makes no practical difference with SQL Server as transactions by default run in auto commit mode anyway.

The only observable difference is that it performs validation that you haven't also tried to pass in an external transaction.

The following will succeed and rollback all batches

var transaction = sourceConnection.BeginTransaction();             
using (SqlBulkCopy bulkCopy =
    new SqlBulkCopy(sourceConnection, SqlBulkCopyOptions.Default, transaction))

{
    bulkCopy.BatchSize = 50;

    bulkCopy.DestinationTableName = "dbo.foobar";
        bulkCopy.WriteToServer(dt);
}

transaction.Rollback();

Passing SqlBulkCopyOptions.UseInternalTransaction fails with an error

Must not specify SqlBulkCopyOption.UseInternalTransaction and pass an external Transaction at the same time.

I wondered if it might make a difference if an SET IMPLICIT_TRANSACTIONS ON; had previously been run on the connection to turn off auto commit mode but the overload of the SqlBulkCopy constructor that takes a connection object returns an "Unexpected existing transaction." error in both cases anyway - and the overload that takes a connection string just creates a new connection.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • What about when one batch in the middle of the process would fail? Without UseInternalTransaction it would abort the following batches, with UseInternalTransaction which wraps every batch in a separate explicit transaction I'd expect it not the abort the following batches, no? – BornToCode Apr 27 '23 at 08:55
  • 1
    @BornToCode - If you have a testable hypothesis why not test it and add an answer if you find something not covered in the existing answers? – Martin Smith Apr 27 '23 at 08:59
0

The remarks for BatchSize describe the distinction:

If the SqlBulkCopy instance has been declared without the UseInternalTransaction option in effect, rows are sent to the server BatchSize rows at a time, but no transaction-related action is taken. If UseInternalTransaction is in effect, each batch of rows is inserted as a separate transaction.

In other words, UseInternalTransaction on means upload in batches and then insert as a whole; UseInternalTransaction off means upload and insert the first batch, then likewise for the second batch, and so on.

Edward Brey
  • 40,302
  • 20
  • 199
  • 253