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();
}
}