SqlBulkCopy: What is the difference between passing SqlBulkCopyOptions.UseInternalTransaction and not passing it?
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?
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.
- Diedit oleh Jürgen Bayer 13 April 2012 10:04 Figures were misleading
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.
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:
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.
- Diedit oleh Jürgen Bayer 16 April 2012 8:43
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.
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."
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)?
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.
As much as I like to offer an answer to the problem, I couldn't. I have great doubt to the way SqlBulkCopy works. I ran into another type of problem though mine was a bit different, our application has a built-in auto-rebuilding indexes, whenever index fragmentation is detected it will run to defrag them. that being said, our import program uses SqlBulkCopy to import data and even when I specified SqlBulkCopyOptions.TableLock our application would still error out randomly whenever the auto re-index code runs, the error stated that schema changes occurred to the destination table, etc. which makes no sense and I wasn't able to find a solution that works yet.
- Diedit oleh nguyenhh 20 Agustus 2013 20:45
I am using SqlBulkCopy in my application, and have also encountered this very problem with the documentation. As Jurgen points out, the documentation describes the same outcome whether you use the "Non-transacted" form or the "Dedicated transaction" form, so the documentation is not useful in clarifying the difference in these two scenarios. So far for me, his explanation is the most useful - that perhaps the switch merely guarantees the use of a transaction when the class is used against non-SQL Server databases. In the case of a SQL Server database, the switch UseInternalTransaction appears to be redundant with not using it (assuming no enlistment or explicit transaction is given). Another clue in the MSDN documentation gave me the impression that using the switch would allow you, somehow, to affect/control the internal transaction such as commit or rollback, but I could not find any members on the SqlBulkCopy class through which to gain access to that internal transaction or to affect a rollback or commit, so that was a dead-end for me.