ExecuteNonQuery() not working to create temp table SqlServer

Question

I am trying to create a temp table from the a select statement so that I can get the schema information from the temp table.

I am able to achieve this in SQL Server with the following code:

//This creates the temp table
SELECT location.id, location.name into #URM_TEMP_TABLE from location

//This retrieves column information from the temp table
SELECT * FROM tempdb.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME like '#U%'

If I run the code in c# like so:

using (CONN = new SqlConnection(Settings.Default.UltrapartnerDBConnectionString))
                            {
                                var commandText = ReportToDisplay.ReportQuery.ToLower().Replace("from", "into #URM_TEMP_TABLE from");

                                using (SqlCommand command = CONN.CreateCommand())
                                {
                                    //Create temp table 
                                    CONN.Open();
                                    command.CommandText = commandText;
                                    int retVal = command.ExecuteNonQuery();
                                    CONN.Close();



                                    //get column data from temp table
                                    command.CommandText = "SELECT * FROM TEMPDB.INFORMATION_SCHEMA.Columns WHERE TABLE_NAME like '#U%'";
                                    CONN.Open();

                                    using (var reader = command.ExecuteReader())
                                    {
                                        while (reader.Read())
                                        {
                                            ColumnsForReport.Add(new ListBoxCheckBoxItemModel
                                            {
                                                Name = reader["COLUMN_NAME"].ToString(),
                                                DataType = reader["DATA_TYPE"].ToString(),
                                                IsSelected = false,
                                                RVMCommandModel = this
                                            });
                                        }
                                    }

                                    CONN.Close();

                                    //drop table
                                    command.CommandText = "DROP TABLE #URM_TEMP_TABLE";
                                    CONN.Open();
                                    command.ExecuteNonQuery();
                                    CONN.Close();
                                }
                            }

Everything works until it gets to the drop statement: Cannot drop the table '#URM_TEMP_TABLE'

So ExecuteNonQuery returns back 2547 - which is the number of rows the temp table is supposed to have in it. However, it seems that the table does not actually get created using this. Is ExecuteNonQuery the right method to call?

Answer 1

temporary tables are only in scope for the current session, in the code you've posted you're opening a connection, creating a temp table, closing connection.

then opening another connection (new session) and attempting to drop a table which is not in scope of that session.

You would need to drop the temp table within the same connection, or possibly make it a global temp table (##) - though in this case with two separate connections, a global temp table would still fall out of scope.

Additionally, as it was pointed out in the comments your temp tables will be cleaned up automatically - but if you really did want to drop them, you must do so from the session that created them.

EDIT taken from another SO thread:

Global temporary tables in SQL Server

Global temporary tables operate much like local temporary tables; they are created in tempdb and cause less locking and logging than permanent tables. However, they are visible to all sessions, until the creating session goes out of scope (and the global ##temp table is no longer being referenced by other sessions). If two different sessions try the above code, if the first is still active, the second will receive the following:

Server: Msg 2714, Level 16, State 6, Line 1 There is already an object named '##people' in the database.

I have yet to see a valid justification for the use of a global ##temp table. If the data needs to persist to multiple users, then it makes much more sense, at least to me, to use a permanent table. You can make a global ##temp table slightly more permanent by creating it in an autostart procedure, but I still fail to see how this is advantageous over a permanent table. With a permanent table, you can deny permissions; you cannot deny users from a global ##temp table.

Looks like global temp tables still go out of scope... they're just bad to use in general IMO. Can you just drop the table in the same session or rethink your solution?