Solved

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

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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

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…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

679 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