SQL Server 2012 - Bulk insert error - This operation conflicts with another pending operation on this transaction

Question

We are doing a bulk insert operation using a C# component.

This is the code:

using (SqlCommand sqlCommand = new SqlCommand("SET XACT_ABORT ON", _sqlConnection))
{
    sqlCommand.SafeExecuteNonQuery();
}

var sqlBulkCopy = new SqlBulkCopy(_sqlConnection, bulkCopyOptions, null);

sqlBulkCopy.WriteToServer(table);

The following error occurs:

This operation conflicts with another pending operation on this transaction. The operation failed.

Stack trace:

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlBulkCopy.RunParser(BulkCopySimpleResultSet bulkCopyHandler)
at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsyncContinuedOnSuccess(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)
at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsyncContinued(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)
at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsync(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)
at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestContinuedAsync(BulkCopySimpleResultSet internalResults, CancellationToken cts, TaskCompletionSource`1 source)
at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestAsync(CancellationToken cts, TaskCompletionSource`1 source)
at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalAsync(CancellationToken ctoken)
at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServerAsync(Int32 columnCount, CancellationToken ctoken)
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState)

When I debug the code and I skip over the code that executes the SET XACT_ABORT ON statement, then it suddenly works and keeps working.

When I restore the database and try again I get the error again.

The bulk insert just inserts one record in a table.

Does anyone has an idea what can be causing the problem?

Answer 1

Does "SqlBulkCopyOptions.CheckConstraints" in your bulk copy options fix the problem? In my case the table I was doing the bulk copy to had a foreign key to a table I was inserting a record to within the same transaction. If that is your case, you may want to have your bulk copy table not check for existing records in the foreign key going to the other table.

Answer 2

As per the accepted answer, setting SqlBulkCopyOptions.CheckConstraints fixed it for me.

        Dim copyOptions As SqlBulkCopyOptions = SqlBulkCopyOptions.CheckConstraints 

        Using bulkCopy As New SqlBulkCopy(xcn.Connection, copyOptions, trans)
            bulkCopy.DestinationTableName = MainTable
            bulkCopy.WriteToServer(dt)
        End Using