none
Disposed TransactionScope causing future queries to fail RRS feed

  • Question

  • Okay, either I am doing something wrong or there is a bug in the Transaction code. The specific error I am getting is this:

    The requested operation could not be performed because OLE DB provider \"MSDASQL\" for linked server \"SERVER_NAME\" does not support the required transaction interface.

    The process is designed to bring in data from different databases to create a crosswalk for future lookups. So it iterates though account names, pulls in data and inserts it into a crosswalk table. Everything works okay on the first pass, it's the 2nd pass where the error is thrown. If I comment out the InsertData(...) method, all attempts at GetDataFromLinkedServer(...) execute without issue. I just don't understand why I am getting the error, or how to fix it without removing the Transaction. I have even tried to use the TransactionScope.Suppress without any luck.

    Any help would be much appreciated.

        void StartProcess(string[] accountNames)
        {
            try
            {
                TruncateCrosswalkTable();
    
                foreach (string accountName in accountNames)
                {
                    // GetDataFromLinkedServer works on the first account name
                    int recordsInserted;
                    DataSet dataSet = GetDataFromLinkedServer(accountName);
                    InsertData(dataSet, accountName, out recordsInserted);
                }
            }
            catch (Exception ex)
            {
                // handle exception
            }
        }
    
        DataSet GetDataFromLinkedServer(string accountName)
        {
            try
            {
                // Checking the transaction status reveals that at this point (on all passes)
                // System.Transactions.Transaction.Current is null
    
                // I have tried with and without the TransactionScopeOption.Suppress
                using (var scope = new System.Transactions.TransactionScope(TransactionScopeOption.Suppress))
                {
                    // execute dataset query via OpenQuery to linked server
                    // via 'Microsoft.Practices.EnterpriseLibrary.Data'
                    var cmd = new SqlCommand("select * from openquery(link_server_name, 'query text')");
                    SqlDatabase db = new SqlDatabase(connectionString);
                    var dataSet = db.ExecuteDataSetQuery(cmd); //<-- calling this method on the 2nd account name throws the error.
                    scope.Complete();
                    return dataSet;
                }
            }
            catch (Exception ex)
            {
                // handle exception
            }
        }
    
        void InsertData(DataSet dataSet, string accountName, out int recordsInserted)
        {
            recordsInserted = 0;
    
            using (var scope = new System.Transactions.TransactionScope())
            {
                var db = new SqlDatabase(connectionString);
    
                SqlCommand cmd = null;
    
                string sql = string.Empty;
    
                for (int i = 0; i < dataSet.Tables[0].Rows.Count; i++)
                {
                    var dataRow = dataSet.Tables[0].Rows[i];
                        string ssn = Csrt.Data.Get.String(dataRow["TIN_NUM"], null);
                        string caseName = Csrt.Data.Get.String(dataRow["CASE_NAME"], null).Trim();
    
                    sql += string.Format(
                        "INSERT INTO [Call SSN Crosswalk] (SSN, CaseName, Account) VALUES ('{0}', '{1}', '{2}');\n",
                        ssn.Replace("'", "''"), caseName.Replace("'", "''"), accountName);
    
                    cmd = new SqlCommand(sql);
    
                    recordsInserted += db.ExecuteNonQuery(cmd);
                }
                scope.Complete();
            }
        }

    Monday, September 8, 2014 1:58 PM

Answers

  • Hello mangomadness,

    >>The process is designed to bring in data from different databases to create a crosswalk for future lookups

    You mean that you have many databases as SQL Server, MySql and Oracle work together. If it is, you could check this article, this error is caused by SQL Server "Linked Servers" option does not support making INSERT, UPDATE, or DELETE calls to the database being linked to unless the connection supports transactions. Please make sure that both the database that you are connecting to and that the ODBC driver used in conjuntion with MSDASQL to provide the link to the database support transactions.

    I think issue is more related with the SQL Server database, I would suggest that you could post it to the SQL Server forum to know the capabilities and limitations of the MS SQL Server "Linked Servers" configuration.

    Best Regards,

    Fred.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Proposed as answer by Cor Ligthert Thursday, September 11, 2014 12:33 PM
    • Marked as answer by Fred BaoModerator Monday, September 22, 2014 7:50 AM
    Tuesday, September 9, 2014 6:07 AM
    Moderator