Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

New transaction is not allowed because there are other threads running in the session Error

Posted on 2012-12-31
4
Medium Priority
?
2,595 Views
Last Modified: 2013-01-02
I receive the following error when I try and initiate an SQL Transaction.  From looking at various posts, the problem seems to be because I have a Reader open when I try and initiate the transaction.  Given the following code, can someone suggest a workaround to the problem?  I am using VS2010, C#, SQL Server 2005.

The code performs the following:

Select CVLIDs from the master table that are less than the archive date.
For each CVLID
    Retrieve the CVLID Data (comes from 3 different tables)
    Insert related fields into the 3 Stats tables
    Delete the CVL Data from the main tables (this has not been implemented yet in the code)

I want to use transaction processing so if an insert fails in one table, the data will be rolled back and the data will not be deleted from the main tables.  I want the transaction to be on each individual CVL so that if one fails it won't prevent the archiving of the others.  This will be run on a daily basis and expect 400 - 500 records to be archived daily.  When it first runs there will be over 1500 records to be archived.

Any help is greatly appreciated!

        public Int32 ArchiveCVLs(DateTime archivedate)
        {
            // This function archives the CVLs into the CVL Stats tables.
            SqlDataReader reader;
            Int32 cvlid, retVal;
            bool errFlg = false;
            string tmpErrorMsg = "";
            Int32 cnt = 0;

            try
            {
                // Define connection object
                SqlConnection connection = null;

                // Open Connection to Database
                connection = OpenDatabase();
                if (connection == null)
                {
                    return 0;
                }

                // Create Command object
                SqlCommand command = connection.CreateCommand();
                SqlCommand command2 = connection.CreateCommand();
                command.Connection = connection;
                command2.Connection = connection;

                // Retrieve CVLs to be archived
                command.CommandText = "SELECT CVL_Policy.CVLID " +
                                      "FROM CVL_Policy " +
                                      "JOIN CVL_Submitter ON CVL_Policy.CVLID = CVL_Submitter.CVLID " +
                                      "WHERE IsClosed = @IsClosed AND DateTimeSubmitted < @reportdate";
                command.Parameters.Clear();
                command.Parameters.Add("@IsClosed", SqlDbType.Bit);
                command.Parameters["@IsClosed"].Value = true;
                command.Parameters.Add("@reportdate", SqlDbType.DateTime);
                command.Parameters["@reportdate"].Value = archivedate;
                reader = command.ExecuteReader();

                // For each CVL, retrieve the CVL Data, update the Stats tables and then delete the CVL
                SqlTransaction transaction;
                while (reader.Read())
                {
                    // Start a local transaction
                    transaction = connection.BeginTransaction("ArchiveCVL");
                    command2.Transaction = transaction;
                    cvlid = Convert.ToInt32(reader["CVLID"]);

                    try
                    {
                        if (RetrieveCVLData(cvlid))
                        {
                            cnt++;
                            // CVL Data found, insert into Stats Tables
                            command2.Parameters.Clear();
                            command2.CommandText = "INSERT INTO CVL_Stats_Transmit " +
                                                    "(CVLID, FaxNumber, FaxTransmitAttempts, FaxIsTransmitted, faxtransmitdate, " +
                                                    "EmailAddr, EmailTransmitAttempts, EmailIsTransmitted, EmailTransmitDate) " +
                                                    "VALUES(@cvlid, @faxnumber, @faxtransmitattempts, @faxistransmitted, @faxtransmitdate, " +
                                                    "@emailaddr, @emailtransmitattempts, @emailistransmitted, @emailtransmitdate);";
                            command2.Parameters.Add("@cvlid", SqlDbType.Int);
                            command2.Parameters["@cvlid"].Value = cvlid;
                            command2.Parameters.Add("@faxnumber", SqlDbType.NChar, 255);
                            command2.Parameters["@faxnumber"].Value = FaxNumber;
                            command2.Parameters.Add("@faxtransmitattempts", SqlDbType.Int);
                            command2.Parameters["@faxtransmitattempts"].Value = FaxTransmitAttempts;
                            command2.Parameters.Add("@faxistransmitted", SqlDbType.Bit);
                            command2.Parameters["@faxistransmitted"].Value = FaxIsTransmitted;
                            command2.Parameters.Add("@faxtransmitdate", SqlDbType.DateTime);
                            if (String.IsNullOrEmpty(FaxTransmitDate))
                                command2.Parameters["@faxtransmitdate"].Value = DBNull.Value;
                            else
                                command2.Parameters["@faxtransmitdate"].Value = FaxTransmitDate;
                            command2.Parameters.Add("@emailaddr", SqlDbType.NChar, 255);
                            command2.Parameters["@emailaddr"].Value = EmailAddr;
                            command2.Parameters.Add("@emailtransmitattempts", SqlDbType.Int);
                            command2.Parameters["@emailtransmitattempts"].Value = EmailTransmitAttempts;
                            command2.Parameters.Add("@emailistransmitted", SqlDbType.Bit);
                            command2.Parameters["@emailistransmitted"].Value = EmailIsTransmitted;
                            command2.Parameters.Add("@emailtransmitdate", SqlDbType.DateTime);
                            if (EmailTransmitDate == null)
                                command2.Parameters["@emailtransmitdate"].Value = DBNull.Value;
                            else
                                command2.Parameters["@emailtransmitdate"].Value = EmailTransmitDate;
                            try
                            {
                                retVal = command2.ExecuteNonQuery();
                            }
                            catch (Exception ex1)
                            {
                                retVal = 0;
                                tmpErrorMsg = tmpErrorMsg + "Error Inserting into CVL_Stats_Transmit CVLID (" + cvlid.ToString() + ").  " + ex1.Message + "<br />";
                                cnt--;
                                if (cnt < 0)
                                    cnt = 0;
                                cvlid = 0;
                                errFlg = true;
                            }
                            if (retVal == 1)
                            {
                                // Insert successful
                                command2.Parameters.Clear();
                                command2.CommandText = "INSERT INTO CVL_Stats_Submitter " +
                                                        "(CVLID, SubmitterEmail, DateTimeSubmitted, SubmitterName) " +
                                                        "VALUES(@cvlid, @submitteremail, @datetimesubmitted, @submittername);";
                                command2.Parameters.Add("@cvlid", SqlDbType.Int);
                                command2.Parameters["@cvlid"].Value = cvlid;
                                command2.Parameters.Add("@submitteremail", SqlDbType.NChar, 50);
                                command2.Parameters["@submitteremail"].Value = SubmitterEmail;
                                command2.Parameters.Add("@datetimesubmitted", SqlDbType.DateTime);
                                if (String.IsNullOrEmpty(DateTimeSubmitted))
                                    command2.Parameters["@datetimesubmitted"].Value = DBNull.Value;
                                else
                                    command2.Parameters["@datetimesubmitted"].Value = DateTimeSubmitted;
                                command2.Parameters.Add("@submittername", SqlDbType.NChar, 50);
                                command2.Parameters["@submittername"].Value = SubmitterName;
                                try
                                {
                                    retVal = command2.ExecuteNonQuery();
                                }
                                catch (Exception ex2)
                                {
                                    retVal = 0;
                                    tmpErrorMsg = tmpErrorMsg + "Error inserting into CVL_Stats_Submitter CVLID (" + cvlid.ToString() + ").  " + ex2.Message + "<br />";
                                    cvlid = 0;
                                    errFlg = true;
                                }
                                if (retVal == 1)
                                {
                                    // Insert successful
                                    command2.Parameters.Clear();
                                    command2.CommandText = "INSERT INTO CVL_Stats_Policy " +
                                                            "(CVLID, StateName, PolicyNumber, AssignedAgent, " +
                                                            "DateTimeAssigned, IsClosed, DateTimeClosed) " +
                                                            "VALUES(@cvlid, @statename, @policynumber, @assignedagent, " +
                                                            "@datetimeassigned, @isclosed, @datetimeclosed);";
                                    command2.Parameters.Add("@cvlid", SqlDbType.Int);
                                    command2.Parameters["@cvlid"].Value = cvlid;
                                    command2.Parameters.Add("@statename", SqlDbType.NChar, 2);
                                    command2.Parameters["@statename"].Value = StateName;
                                    command2.Parameters.Add("@policynumber", SqlDbType.NChar, 255);
                                    command2.Parameters["@policynumber"].Value = PolicyNumber;
                                    command2.Parameters.Add("@assignedagent", SqlDbType.Int);
                                    command2.Parameters["@assignedagent"].Value = AssignedAgent;
                                    command2.Parameters.Add("@datetimeassigned", SqlDbType.DateTime);
                                    if (String.IsNullOrEmpty(DateTimeAssigned))
                                        command2.Parameters["@datetimeassigned"].Value = DBNull.Value;
                                    else
                                        command2.Parameters["@datetimeassigned"].Value = DateTimeAssigned;
                                    command2.Parameters.Add("@isclosed", SqlDbType.Bit);
                                    command2.Parameters["@isclosed"].Value = IsClosed;
                                    command2.Parameters.Add("@datetimeclosed", SqlDbType.DateTime);
                                    if (String.IsNullOrEmpty(DateTimeClosed))
                                        command2.Parameters["@datetimeclosed"].Value = DBNull.Value;
                                    else
                                        command2.Parameters["@datetimeclosed"].Value = DateTimeClosed;
                                    try
                                    {
                                        retVal = command2.ExecuteNonQuery();
                                    }
                                    catch (Exception ex3)
                                    {
                                        retVal = 0;
                                        tmpErrorMsg = tmpErrorMsg + "Error inserting into CVL_Stats_Policy CVLID (" + cvlid.ToString() + ").  " + ex3.Message + "<br />";
                                        cvlid = 0;
                                        errFlg = true;
                                    }
                                    if (retVal == 0)
                                    {
                                        errFlg = true;
                                        tmpErrorMsg = tmpErrorMsg + "Error Insert data into CVL_Stats_Policy (CVLID=" + cvlid.ToString() + ").<br />";
                                    }

                                }
                                else
                                {
                                    errFlg = true;
                                    tmpErrorMsg = tmpErrorMsg + "Error Insert data into CVL_Stats_Submitter (CVLID=" + cvlid.ToString() + ").<br />";
                                }
                            }
                            else
                            {
                                errFlg = true;
                                tmpErrorMsg = tmpErrorMsg + "Error Insert data into CVL_Stats_Transmit (CVLID=" + cvlid.ToString() + ").<br />";
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        tmpErrorMsg = tmpErrorMsg + "There was a problem archiving CVLID (" + cvlid.ToString() + ").  " + ex.Message + "<br />";
                        cnt--;
                        if (cnt < 0)
                            cnt = 0;
                        cvlid = 0;
                        errFlg = true;
                    }
                    // Attempt to commit the transaction
                    if (errFlg == false)
                        transaction.Commit();
                    else
                        transaction.Rollback();
                }
                if (tmpErrorMsg != "")
                    ErrorMessage = tmpErrorMsg + "  Please contact Intranet Team to report the problem.";
                else
                    ErrorMessage = "";
                reader.Close();
                connection.Close();
                connection.Dispose();
                command.Dispose();
                reader.Dispose();

                return cnt;
            }
            catch (Exception e)
            {
                ErrorMessage = "There was a problem archiving CVLs.  " + e.Message + "  Please contact Intranet Team to report the problem.";
                return 0;
            }
        }

Open in new window

0
Comment
Question by:dyarosh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 43

Accepted Solution

by:
Eugene Z earned 2000 total points
ID: 38734021
0
 
LVL 18

Expert Comment

by:Gary Davis
ID: 38734628
Since it does not like the opened reader, try one of these ideas:

1. Read all the data and close the reader (perhaps use a DataTable) and then iterate on the disconnected collection.

2. Use a different SqlConnection for the transaction. Then the first connection for the collection is not interfering with the second transaction connection.

Gary Davis
0
 

Author Comment

by:dyarosh
ID: 38737251
EugeneZ -

I had reviewed those links before posting my question but did not find them helpful because I am not using EF.
0
 

Author Closing Comment

by:dyarosh
ID: 38737253
I endued up using the DataTable solution.  Thanks for the help.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question