Global Temp Table issue

[Mike_]

I am attempting to create a global temp table to be used between two sessions being called from a webservice.

When I attempt to access the temp table from the second session it can not be found. The reason I am using a global temp table is because the initial session is created on a before event handler which calls the service and then from an after event handler.

I have checked that the table is still available after the initial session closes, and I am still able toe query the data.

Any help would be greatly appreciated!

public void SetUpdateHolds(string SOPnumber, int SOPType, string companyId, string compDatabaseName)
            {
                
                
                SqlConnection conn = new SqlConnection(connString.StrEPConnString);
                conn.Open();
                

                try
                    {


                        string sql = " USE [" + compDatabaseName + "]" +
                                     "CREATE TABLE ##HOLDS (HOLDID varchar (20)) ";
                                     
                        SqlCommand command = conn.CreateCommand();
                        command.CommandText = sql;
                        command.ExecuteNonQuery();
                        
                        sql = 
                                "INSERT INTO ##HOLDS " +
                                "SELECT PRCHLDID from SOP10104 WHERE SOPNUMBE = '" + SOPnumber + "' AND DELETE1 = '0' AND  SOPTYPE ='" + SOPType + "'";


                        command.CommandText = sql;
                        command.ExecuteNonQuery();
                        command.Dispose();
                        conn.Close();
                        conn.Dispose();
          
                    }
                    catch (SqlException e)
                    {

                        util.Log("Error creating temp table after canceling RemoveHold " + SOPnumber + e.Message + e.StackTrace);
                        conn.Close();
                        conn.Dispose();
                       
                    }

            }

            public void UpdateHolds(string SOPnumber, int SOPType, string companyId, string compDatabaseName)
            {

                Collection<CCTransaction> list = new Collection<CCTransaction>();
                SqlConnection conn = new SqlConnection(connString.StrEPConnString);
                conn.Open();
                string result = "";


                try
                {

                    string sql = "USE [" + compDatabaseName + "] UPDATE SOP10104 SET DELETE1 = 0 WHERE PRCHLDID = (SELECT  HOLDID FROM ##HOLDS)";
                                 
                           

                    SqlCommand command = conn.CreateCommand();
                    command.CommandText = sql;
                    SqlDataReader reader = command.ExecuteReader();
                    command.ExecuteNonQuery();
                 
                           
                    sql = "DROP TABLE ##HOLDS"; 

                    
                    command.ExecuteNonQuery();
                    command.Dispose();
                    conn.Close();
                    conn.Dispose();

                }
                catch (SqlException e)
                {

                    util.Log(result + "   Error  updating PRCHLDID in Table SOP10104 after canceling RemoveHold " + SOPnumber + e.Message + e.StackTrace);
                    conn.Close();
                    conn.Dispose();

                }




            }

[thines01]

You need to call new on your new SQL commands.
Also, don't you get an Exception or an invalid state when the code hits the ExecuteReader followed by the ExecuteNonQuery?