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