Temp table not visible in same SqlConnection

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

 

[Steve Willcock]

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

 

[bruce barker]

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)