SqlBulkCopy: What is the difference between passing SqlBulkCopyOptions.UseInternalTransaction and not passing it?

Question

Hi,

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: www.juergen-bayer.net/downloads/SqlBulkCopy-Error-and-Transaction-Test.zip

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

Edit:
The figures I used were a bit misleading. If BatchSize is 10 and I add 100 records where record number 45 (I changed this number) is faulty, I get 40 records in the database, even if UseInternalTransaction is not passed. If no transaction would be used I would get 44 records, right? But since I get 40 (four batches of 10) it must be the case that each batch is executed within a transaction.

B.T.W: I know the documentation which says:
"When specified, each batch of the bulk-copy operation will occur within a transaction. If you indicate this option and also provide a SqlTransaction object to the constructor, an ArgumentException occurs."

I do not understand that because obviously we get transactions anyway.

Answer 1

Hello,

Please, could you have a look at http://www.bing.com/search?q=useinternaltransaction&src=IE-SearchBox&Form=IE8SRC 

You will find several links related to your problem , which can be a beginning for a more precise explanation.

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 2

Hello Papy,

thanks for your reply but it seems as if no-one can really answer my question (even on Stackoverflow) and just refers to the documentation or the Internet. Be ensured that I read the docs and searched on the internet (well, on Google, not on Bing). The documentation for .NET 4.0 states that:

"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."

That would mean: If I have lets say a BatchSize of 3 and not pass UseInternalTransaction, the SQL Server would get for example following SQL statements:
INSERT INTO Products (Id, Name, CategoryID, Price) VALUES (1, 'Test 1', 1, 100)
INSERT INTO Products (Id, Name, CategoryID, Price) VALUES (2, 'Test 2', 0, 100)
INSERT INTO Products (Id, Name, CategoryID, Price) VALUES (3, 'Test 3', 1, 100)

The second record is faulty because a category 0 does not exist. If I execute this batch of SQL statements directly against the SQL Server 2012 using the SQL Server Management Studio I get 2 records in result and one error. That's because no transaction is used. I think, we agree in that.

But if I do the same using SqlBulkCopy (passing SqlBulkCopyOptions.CheckConstraints), I get 0 (Zero) records in the database. If there was no transaction I would get 2 records like if I executed the statements directly. The obvious conclusion is that there must be a transaction, even if I do not pass UseInternalTransaction! Or how do you explain that I get 0 records?

B.T.W.: I even tried using much larger figures like inserting 1,000,000 records with a batch size of 100,000 with same result: Either the whole batch is committed to the database or no record.

Regards

Jürgen

Answer 3

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. So far the documentation is right. 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 (2012), but to be on the safe side I would set it.

Answer 4

hi, plz go through the below link. This may clear your doubts.

http://msdn.microsoft.com/en-us/library/tchktcdk%28v=vs.80%29.aspx

Answer 5

Hi,

thanks, but how could that clear any doubts? What is the logical difference between

a) Performing a Non-transacted Bulk Copy Operation: "All batches copied up to the point of the error are committed; the batch containing the duplicate key is rolled back, and the bulk copy operation is halted before processing any other batches."

and

b) Performing a Dedicated Bulk Copy Operation in a Transaction: "All batches copied up to the point of the error are committed; the batch containing the duplicate key is rolled back, and the bulk copy operation is halted before processing any other batches."

The text in both subjects is identical! Even for "Performing a Non-transacted Bulk Copy Operation" the documentation says that the batch containing the error is "rolled back". How can a batch be rolled back if it was not in a transaction?

Do you mean I should have seen in the documentation that a transaction is used anyway? Welll. That is a kind of hidden information if so. But even then my actual question is not answered: What is the pupose of SqlBulkCopyOptions.UseInternalTransaction? To have an (internal) own transaction and not use the implicit transaction of the database system? What for? I rather would say that the purpose is to enforce a transaction even if the database system (which can be another than the SQL Server) does not execute a TDS batch in an implicit transaction. But that's unfortunately not documented.

I would really appreciate if someone just could answer this question and not pass another link to the documentation.

Sorry, but is answering here just about giving links to documentations (probably to gain points)?

Answer 6

Here's a thought. Maybe the UseInternalTransaction setting can keep the SqlBulkCopy class from auto-enlisting in a transaction when auto-enlist has been enabled in the connection string? I haven't actually tested this, but supposedly SqlBulkCopy does support transaction auto-enlistment so perhaps the UseInternalTransaction setting is provided to enable the caller to opt-out of auto-enlistment. That would also explain why UseInternalTransaction makes no difference when used with a connection string that does not have auto-enlistment enabled.

Answer 7

Did you find an answer to this question?