Solved

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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 article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

809 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