Solved

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

Posted on 2012-12-31
4
2,223 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
  • 2
4 Comments
 
LVL 42

Accepted Solution

by:
EugeneZ 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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now