Is SqlServer BCP all or nothing?

bcp c# sqlbulkcopy sql-server

Question

We have some critical code that relies on the .NET SqlBulkCopy API. Occasionally, this code will fail with the following error:

System.Data.SqlClient.SqlException : Insert bulk failed due to a schema change of the target table.

This blog post suggests that this is a transient, nearly unpreventable error. What I'm wondering is, when this error happens, is it guaranteed that no rows have been inserted (if so, I can simply catch this exception and retry the BCP operation on the .NET side)?

I am using SqlServer 2008.

Popular Answer

You should use SqlBulkCopy Constructor (SqlConnection, SqlBulkCopyOptions, SqlTransaction) constructor, which takes a parameter for SqlTransaction. Under that instance of SqlTransaction Bulk copy will perform operation and in case of failure you will have the option of rolling back.

You may also see: Transaction and Bulk Copy Operations

Bulk copy operations can be performed as isolated operations or as part of a multiple step transaction. This latter option enables you to perform more than one bulk copy operation within the same transaction, as well as perform other database operations (such as inserts, updates, and deletes) while still being able to commit or roll back the entire transaction.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why