Temp table not visible in same SqlConnection

  • Thread starter Thread starter Vince Apesa
  • Start date Start date
V

Vince Apesa

All,
Any ideas why a temp table isn't visible on the same connection? When
I attempt to run the second sql stmt I get Invalid object name
'#tempTable'

I was under the impression the temp table should exists during the
entire session. Here's an example.

----
SqlConnection = new SqlConnection("....");
conn.Open();

SqlCommand cmd = new SqlCommand("select 1 as xyz into #tempTable",
conn);
cmd.ExecuteNonQuery();

cmd = new SqlCommand("select * from #tempTable", conn);
SqlDataReader r = cmd.ExecuteReader();

while (r.Read())
Console.WriteLine(r["xyz"]);

r.Close();
conn.Close();
 
Vince, except for the fact that this line:

SqlConnection = new SqlConnection("....");

should be

SqlConnection conn = new SqlConnection("....");

this example works in my environment and prints '1' to the Console as
expected.

I'd check your code to make sure you haven't missed something obvious :-)

Steve
 
turn off connection pooling, or use the same command object for all your
queries. becuase each command gets its own connection form the pool, you are
never guaranteed to the same connection (spid) is used for two commands, and
temp tables are tied to the connection (spid)

-- bruce (sqlwork.com)
 
Back
Top