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

dohp0rv5 于 8个月前发布在 SQL Server

关注(0)|答案(2)|浏览(117)

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&amp; 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?

sql-server

来源：https://stackoverflow.com/questions/30916758/sql-server-2012-bulk-insert-error-this-operation-conflicts-with-another-pend

2条答案

按热度按时间

cnwbcb6i1#

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.

赞(0）回复(0）举报 8个月前

zc0qhyus2#

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

赞(0）回复(0）举报 8个月前

我来回答

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

2条答案

相关问题

热门标签

最新问答