Edit Question - KnowledgeHub

Title Body

I'm creating a temporary table and populating it with two separate statements using the same command and connection. However, I'm getting an 'Invalid object name' if I create the table with the parameter inserted before the create. If I add it after the create, it works fine. The temporary table is supposed to last the entire session, so I don't see what it matters when the parameter is added to the command object. FAILS: using (SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=TEST;Integrated Security=True;")) using (SqlCommand cmd = conn.CreateCommand()) { conn.Open(); cmd.Parameters.Add(new SqlParameter("@ID", 1234)); cmd.CommandText = "CREATE TABLE #Test (ID INT NOT NULL PRIMARY KEY, I INT NOT NULL)"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO #Test VALUES (@ID, 1)"; cmd.ExecuteNonQuery(); ..... more code that uses the table } WORKS: using (SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=TEST;Integrated Security=True;")) using (SqlCommand cmd = conn.CreateCommand()) { conn.Open(); cmd.CommandText = "CREATE TABLE #Test (ID INT NOT NULL PRIMARY KEY, I INT NOT NULL)"; cmd.ExecuteNonQuery(); cmd.Parameters.Add(new SqlParameter("@ID", 1234)); cmd.CommandText = "INSERT INTO #Test VALUES (@ID, 1)"; cmd.ExecuteNonQuery(); ..... more code that uses the table } edit: SQL Profiler shed more light on this. If the command has any parameters, the underlying code is issuing an "exec sp_executesql". If the Parameters are cleared, the underlying code issues a more direct "CREATE TABLE". Temp tables are cleaned up after an sp_executesql,

Tags (comma-separated) Cancel