SqlBulkCopy causes Deadlock on SQL Server 2000.
- by megatoast
I have a customized data import executable in .NET 3.5 which the SqlBulkCopy to basically do faster inserts on large amounts of data. The app basically takes an input file, massages the data and bulk uploads it into a SQL Server 2000. It was written by a consultant who was building it with a SQL 2008 database environment. Would that env difference be causing this? SQL 2000 does have the bcp utility which is what BulkCopy is based on. So, When we ran this, it triggered a Deadlock error.
Error details: Transaction (Process ID 58) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
I've tried numerous ways to try to resolve it. like temporarily setting the connection string variable MultipleActiveResultSets=true, which wasn't ideal, but it still gives a Deadlock error. I also made sure it wasn't a connection time out problem.
here's the function. Any advice?
/// <summary>
/// Bulks the insert.
/// </summary>
public void BulkInsert(string destinationTableName, DataTable dataTable)
{
SqlBulkCopy bulkCopy;
if (this.Transaction != null)
{
bulkCopy = new SqlBulkCopy
(
this.Connection,
SqlBulkCopyOptions.TableLock,
this.Transaction
);
}
else
{
bulkCopy = new SqlBulkCopy
(
this.Connection.ConnectionString,
SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.UseInternalTransaction
);
}
bulkCopy.ColumnMappings.Add("FeeScheduleID", "FeeScheduleID");
bulkCopy.ColumnMappings.Add("ProcedureID", "ProcedureID");
bulkCopy.ColumnMappings.Add("AltCode", "AltCode");
bulkCopy.ColumnMappings.Add("AltDescription", "AltDescription");
bulkCopy.ColumnMappings.Add("Fee", "Fee");
bulkCopy.ColumnMappings.Add("Discount", "Discount");
bulkCopy.ColumnMappings.Add("Comment", "Comment");
bulkCopy.ColumnMappings.Add("Description", "Description");
bulkCopy.BatchSize = dataTable.Rows.Count;
bulkCopy.DestinationTableName = destinationTableName;
bulkCopy.WriteToServer(dataTable);
bulkCopy = null;
}
