none
System.InvalidOperationException: The context connection is already in use. RRS feed

  • Question

  • In the SP, I need to access multiple tables at the same time,so I need to create multiple connection, but it failed to do that and throw the error in the title. And when I commented out all the comment but one to keep only one connection, then the SP can run well. So what need I do to access multiple tables?

     

    public static void PrintToday()
        {
            SqlPipe p;
            p = SqlContext.Pipe;
            p.Send(System.DateTime.Today.ToString());
            SqlConnection conn1 = new SqlConnection();
            SqlConnection conn2 = new SqlConnection();
          // SqlConnection conn3 = new SqlConnection();
            conn1.ConnectionString = "Context Connection=true";
            conn2.ConnectionString = "Context Connection=true";
           // conn3.ConnectionString = "Context Connection=true";
            SqlCommand cmd1 = new SqlCommand();
            SqlCommand cmd2 = new SqlCommand();
           // SqlCommand cmd3 = new SqlCommand();
            cmd1.Connection = conn1;
            cmd2.Connection = conn2;
            //cmd3.Connection = conn3;
            cmd1.CommandText = @"select * from RSI_CORE_CFGPROPERTY";
            cmd2.CommandText = @"select * from RSI_CORE_CFGPROPERTY";
            //cmd3.CommandText = @"select * from RSI_CORE_CFGPROPERTY";
            conn1.Open();
            conn2.Open();
            //conn3.Open();

            DataTable dt = new DataTable("king");

            SqlDataReader rdr1 = cmd1.ExecuteReader();
            SqlDataReader rdr2 = cmd2.ExecuteReader();
            //SqlDataReader rdr3 = cmd3.ExecuteReader();
          

            SqlContext.Pipe.Send(rdr2);

            rdr1.Close();
            rdr2.Close();
            //rdr3.Close();
            conn1.Close();
            conn2.Close();
            //conn3.Close();
        }

    Monday, September 26, 2011 3:19 AM

Answers

  • In the SP, I need to access multiple tables at the same time,so I need to create multiple connection, but it failed to do that and throw the error in the title. And when I commented out all the comment but one to keep only one connection, then the SP can run well. So what need I do to access multiple tables?

     

    You can have only one context connection open at a time so you'll need to use regular connections for this.  See the SQL Server Books Online topic on Restrictions on Regular ad Context Conenctions: http://msdn.microsoft.com/en-us/library/ms131101.aspx

     

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Monday, September 26, 2011 3:28 AM