Unexpected existing transaction - SQLBulkCopy

Hi All,

I am using the attached function which bulk inserts data from an Excel spreadsheet into a SQL Server 2008 table.

At the moment when I run this code I receive the following error but cannot work out where my code is failing to cause this problem:

System.InvalidOperationException: Unexpected existing transaction.

Please could anyone make any suggestions?

Many thanks,

Rit
public static void ImportListData(string strFileToImport, int intListID)
    {
        string sSQLTable = "list_item";

        string FileLocation = ConfigurationManager.AppSettings["UploadLocation"] + @"\" + strFileToImport;
        string sExcelFile = FileLocation;
        string sWorkbook = "[My_List$]";

        //Create connection strings
        string sExcelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sExcelFile + ";Extended Properties=\"Excel 8.0;HDR=YES;\"";
        string sSqlConnectionString = Convert.ToString(WebConfigurationManager.ConnectionStrings["DBConn"]);

        //Series of commands to bulk copy data from the excel file into our SQL table

        OleDbConnection OleDbConn = new OleDbConnection(sExcelConnectionString);
        OleDbCommand OleDbCmd = new OleDbCommand(("SELECT * FROM " + sWorkbook), OleDbConn);
        OleDbCmd.CommandTimeout = 300;

        OleDbConn.Open();

        OleDbDataReader dr = OleDbCmd.ExecuteReader();

        //Datatable
        DataTable dt = new DataTable();
        dt.Load(dr);
        dt.Columns.Add("ListID");
        dt.Columns["ListID"].Expression = intyListID.ToString();

        SqlBulkCopy bulkCopy = new SqlBulkCopy(sSqlConnectionString);
        bulkCopy.BulkCopyTimeout = 300;

        SqlBulkCopyColumnMapping GiftListID = new SqlBulkCopyColumnMapping("ListID", "ListID");
        bulkCopy.ColumnMappings.Add(ListID);
        SqlBulkCopyColumnMapping ItemName = new SqlBulkCopyColumnMapping("Item Name", "ItemName");
        bulkCopy.ColumnMappings.Add(ItemName);

        bulkCopy.DestinationTableName = sSQLTable;
        bulkCopy.WriteToServer(dt);

        OleDbConn.Close();

        bulkCopy.Close();
    }

Open in new window

LVL 1
rito1Asked:
Who is Participating?
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
Think you need to explicitly tell sqlbulkcopy to use the transaction....

SqlBulkCopy bulkCopy = new SqlBulkCopy(<connectionstring>, SqlBulkCopyOptions.Default, transaction)

or

SqlBulkCopy bulkCopy = new SqlBulkCopy(<connectionstring>, SqlBulkCopyOptions.UseInternalTransaction)

Have a look at http://msdn.microsoft.com/en-us/library/wftd1yfz.aspx then the examples on MSDN : http://msdn.microsoft.com/en-US/library/tchktcdk(v=VS.80).aspx
0
 
rito1Author Commented:
The error is referring to the following line of code:

bulkCopy.WriteToServer(dt);

The excel file that is being used within my function would have just been uploaded by the user so I am wondering whether a process is still using the Excel file.. Is there a way of ending any processes that could be using the file prior to my import function starts?

Thanks,

Rit
0
 
rito1Author Commented:
... Actually I think the error could be related to the actual SqlBulkCopy transaction in SQL Server.

Has anyone else experienced this before?
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
SiliconXPConnect With a Mentor Commented:
Hi try using one connection at a time to avoid have transactions begun in one spilling over to the new one
public static void ImportListData(string strFileToImport, int intListID)
    {
        string sSQLTable = "list_item";

        string FileLocation = ConfigurationManager.AppSettings["UploadLocation"] + @"\" + strFileToImport;
        string sExcelFile = FileLocation;
        string sWorkbook = "[My_List$]";

        //Create connection strings
        string sExcelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sExcelFile + ";Extended Properties=\"Excel 8.0;HDR=YES;\"";
        string sSqlConnectionString = Convert.ToString(WebConfigurationManager.ConnectionStrings["DBConn"]);

        //Series of commands to bulk copy data from the excel file into our SQL table

        OleDbConnection OleDbConn = new OleDbConnection(sExcelConnectionString);
        OleDbCommand OleDbCmd = new OleDbCommand(("SELECT * FROM " + sWorkbook), OleDbConn);
        OleDbCmd.CommandTimeout = 300;

        OleDbConn.Open();

        OleDbDataReader dr = OleDbCmd.ExecuteReader();

        //Datatable
        DataTable dt = new DataTable();
        dt.Load(dr);
        dt.Columns.Add("ListID");
        dt.Columns["ListID"].Expression = intyListID.ToString();
//we are done with the excel connection.we got data, so close it
OleDbConn.Close();

// then start dealing with the other connection
        SqlBulkCopy bulkCopy = new SqlBulkCopy(sSqlConnectionString);
        bulkCopy.BulkCopyTimeout = 300;

        SqlBulkCopyColumnMapping GiftListID = new SqlBulkCopyColumnMapping("ListID", "ListID");
        bulkCopy.ColumnMappings.Add(ListID);
        SqlBulkCopyColumnMapping ItemName = new SqlBulkCopyColumnMapping("Item Name", "ItemName");
        bulkCopy.ColumnMappings.Add(ItemName);

        bulkCopy.DestinationTableName = sSQLTable;
        bulkCopy.WriteToServer(dt);
//remove take this up
        //OleDbConn.Close();

        bulkCopy.Close();
    }

Open in new window

0
 
rito1Author Commented:
Hi Both, I am just fiddling with this function now and will let you know how I get on.

Thanks,
Rit
0
 
8080_DiverConnect With a Mentor Commented:
I usually upload to a Staging table that has an Identity column as the only unique index/key /PK.  That way, I get the data into the database where I can select the rows to insert based upon the uniqueness constraints of the target table and, if necessary, even LEFT OUTER JOIN to the target table and only insert those rows that don't already exist.
Admittedly, may add a bit of time to the overall process; however, it provides  agood bit more protection from duplicate data blowing the bulk load off.
0
 
rito1Author Commented:
Hi All,

Specifying the transaction was the answer to my nightmare...

http://forums.asp.net/t/1171298.aspx

Thanks all,

Rit
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.