On SQL Server 2000, SqlBulkCopy creates a deadlock.

.net asp.net sql sqlbulkcopy sql-server

Question

In.NET 3.5, I have a customized data import program that uses SqlBulkCopy to essentially do inserts on massive volumes of data more quickly. The program essentially reads data from an input file, manipulates it, then bulk uploads it to a SQL Server 2000. It was created using a SQL 2008 database system and authored by a consultant. Could this be caused by the environment difference? BulkCopy is based on the bcp function, which is included in SQL 2000. As a result, when we run this, a deadlock issue occurred.

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 several different approaches to try and fix it. even though it wasn't the best solution, temporarily changing the connection string variable MultipleActiveResultSets=true still results in a Deadlock problem. I also checked to make sure the issue wasn't a connection timeout.

Here is how it works. Any suggestions?

/// <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;
    }
1
1
5/12/2010 10:20:40 PM

Popular Answer

Finally, I was able to download a 50 gigabyte local copy of our production database to test the program. It turned out that the dealocking only had environmental implications. Thank you, guys.

0
5/13/2010 6:15:25 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow