Solved

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

Posted on 2012-12-31
4
2,505 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 500 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

621 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