ExecuteNonQuery is not working to create TempTable SQL Server

Question

The temp table is not being created in the database. I verified that the credentials have access to create a temp table. Copy and pasted the SQL command and it works in SSMS.

No exceptions are thrown when debugging. The cmd variable has the proper connection and SQL text before executing.

I've used the same connection string in other parts of the app to query the server successfully, so there is no issue there.

My goal is to create a temp table, then populate it via SqlBulkCopy then do a merge update then drop the temp table.

EDIT: My error was referencing the wrong table in the DestinationTableName but moreso that I was checking the progress in SSMS with a separate connection that could not see the temp table. Also, the finally statement is redundant. Thanks all!

        string tmpTable = @"create table #TempTable 
                            (
                            [Column1] [varchar](50) NOT NULL,
                            [Column2] [varchar](50) NOT NULL,
                            [Column3] [varchar](50) NOT NULL
                            )";
        string connString = "Data Source=AzureDBServer;" + 
                                "Initial Catalog=Database;" + 
                                "User id=UserId;" + 
                                "Password=Password;";

        using (SqlConnection connection = new SqlConnection(connString))
        {
            connection.Open();
            SqlCommand cmd = new SqlCommand(tmpTable, connection);
            cmd.ExecuteNonQuery();

            try
            {
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
                {
                    bulkCopy.DestinationTableName = "#TempTable";
                    bulkCopy.WriteToServer(dataTable);

                    string mergeSql = "<Will eventually have merge statement here>";

                    cmd.CommandText = mergeSql;
                    int results = cmd.ExecuteNonQuery();

                    cmd.CommandText = "DROP TABLE #TempTable";
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception)
            {
                throw;
            }

            finally
            {
                SqlCommand final = new SqlCommand("DROP TABLE #TempTable", connection);
                final.ExecuteNonQuery();

            }

        }

How do you check whether the table was created? It is possible that the table is not visible when you use a different user to check for it than the one used by the application. — NineBerry, Jul 03 '19 at 14:52
temp tables are only created for the lifetime of the session. In the case of ADO.NET, that's the lifetime of the command. — Heretic Monkey, Jul 03 '19 at 14:52
@HereticMonkey no, temp tables on a SqlClient ADO.NET connection last for the lifetime of the connection, not the command (note: SPs have different scope rules); ***table locals*** are per-command (i.e. `declare @foo table (...)`) — Marc Gravell, Jul 03 '19 at 14:53
Your `DestinationTableName` is `TempTIESNoms`, which you're not (explicitly) creating anywhere. That might be intended, but it's not clear from your question. That said, I seem to recall `SqlBulkCopy` may not actually work on (local) temp tables because it tries some metadata discovery mojo that will only work on real tables. YMMV, but you may need to create a "real" table with a temp name (like `MyTemp` + `Guid.NewGuid`). — Jeroen Mostert, Jul 03 '19 at 14:53
@MarcGravell Did that change at some point? I mean, I use Dapper for everything now, so I forget how raw ADO.NET works ;). — Heretic Monkey, Jul 03 '19 at 14:55
@HereticMonkey I'm not aware of any time that this has *not* been the case; note: if you close/open a connection, it is reset (and may be using a different underlying connection) - so if you're using Dapper to open/close your connections automatically - then it will *effectively* be per command, but that's just because the connection is only *open* for the duration of the command — Marc Gravell, Jul 03 '19 at 14:57
@BenV with the edit, you're **still** talking to a different table; `TempTable` and `#TempTable` are different tables — Marc Gravell, Jul 03 '19 at 14:58
@NineBerry I'm checking in SSMS using the same credentials, getting the "Invalid object name '#TempTable'" error after the code completes — Ben V, Jul 03 '19 at 14:58
Checking in SSMS is definitely not possible, because that's a new session that will not see your table, which is local to your program's session (not merely your credentials). You can use a global temp table (`##TempTable`) but be aware that such tables are, well, global, and hence subject to name clashes. — Jeroen Mostert, Jul 03 '19 at 15:00

score 3 · Accepted Answer · answered Jul 03 '19 at 15:00

3

I'm checking in SSMS using the same credentials, getting the "Invalid object name '#TempTable'" error after the code completes

That's because SSMS is using a different connection, and temp-tables like #Foo are per-connection. You cannot access a #Foo temp-table from any other connection.

It sounds like you want a global temp-table. This is as simple as naming it ##Foo instead of #Foo. Global temp-tables are shared over all connections.

answered Jul 03 '19 at 15:00

Marc Gravell

1,026,079
266
2,566
2,900

If I comment out the merge portion and go directly to the Drop table command, I get the error "Cannot drop the table ... because it does not exist or you do not have permission." I figured that was because the table wasn't created. What am I missing? – Ben V Jul 03 '19 at 15:08
@BenV: you drop the table *twice* -- once in your main body, then again in your `finally`. The latter one will fail. – Jeroen Mostert Jul 03 '19 at 15:18
@Jeroen Mostert Ohhh of course, I knew it had to be something stupid simple. Thanks! – Ben V Jul 03 '19 at 15:29

David Browne - Microsoft · Answer 2 · 2019-07-03T15:17:31.280

The problem is a simple typo as @MarcGravell pointed out in a comment.

bulkCopy.DestinationTableName = "TempTable";

should be

bulkCopy.DestinationTableName = "#TempTable";

Temp tables have session lifetime if not created in a nested batch or stored procedure.

EG this works:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace sqlclienttest
{
    class Program
    {
        static void Main(string[] args)
        {
            string tmpTable = @"create table #TempTable 
                                (
                                [Column1] [varchar](50) NOT NULL,
                                [Column2] [varchar](50) NOT NULL,
                                [Column3] [varchar](50) NOT NULL
                                )";
            string connString = "Data Source=xxxx.database.windows.net;" +
                                    "Initial Catalog=Adventureworks;" +
                                    "User id=xxxxxx;" +
                                    "Password=xxxxxx;";

            var dataTable = new DataTable();
            dataTable.Columns.Add("Column1", typeof(string));
            dataTable.Columns.Add("Column2", typeof(string));
            dataTable.Columns.Add("Column3", typeof(string));

            dataTable.BeginLoadData();
            for (int i = 0; i < 10000; i++)
            {
                var r = dataTable.NewRow();
                r[0] = $"column1{i}";
                r[1] = $"column2{i}";
                r[2] = $"column3{i}";
                dataTable.Rows.Add(r);
            }
            dataTable.EndLoadData();

            using (SqlConnection connection = new SqlConnection(connString))
            {
                connection.Open();
                SqlCommand cmd = new SqlCommand(tmpTable, connection);
                cmd.ExecuteNonQuery();

                try
                {
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
                    {
                        bulkCopy.NotifyAfter = 1000;
                        bulkCopy.SqlRowsCopied += (s, a) => Console.WriteLine($"{a.RowsCopied} rows");
                        bulkCopy.DestinationTableName = "#TempTable";
                        bulkCopy.WriteToServer(dataTable);

                        //string mergeSql = "<Will eventually have merge statement here>";

                        //cmd.CommandText = mergeSql;
                        //int results = cmd.ExecuteNonQuery();

                        cmd.CommandText = "DROP TABLE #TempTable";
                        cmd.ExecuteNonQuery();
                    }
                }
                catch (Exception)
                {
                    throw;
                }


            }
        }
    }
}

The only caviat is that if the client driver decides to wrap your CREATE TABLE statement in sp_executesql or somesuch, the temp table will have nested batch lifetime, not session lifetime. But System.Data.SqlClient doesn't do this unless you put parameters in batch that creates the temp table.

ExecuteNonQuery is not working to create TempTable SQL Server

2 Answers2