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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Mark WillsTopic 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

SiliconXPCommented:
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_DiverCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.