ExecuteScalar on temp table throwing exception

Question

I have a recordset that should be full but isn't. I am working to determine where the load process is failing (i.e. my join produces no results). To be sure I am following the MS example at
system.data.sqlclient.sqlbulkcopy.writetoserver

                        long countStart = System.Convert.ToInt32(  
                            sqlCmdTemp.ExecuteScalar());  
                        Console.WriteLine("Starting row count = {0}", countStart);  
  
                        long countEnd = System.Convert.ToInt32(  
                                sqlCmdTemp.ExecuteScalar());  
                        Console.WriteLine("Ending row count = {0}", countEnd);  

The first piece of the code runs fine but the countEnd section throws an error:

It doesn't matter where I place the 2nd section. Calling the ExecuteScalor() more than once creates the grief. I believe that I am following the example faithfully.

Regards,
Lance

Answer 1

SQL temp tables, table that start with an #, exist for the duration of a connection. According to the error the code is trying to create the same temp table, #tempTable, in the same connection. One solution might be to check if the #tempTable exists before trying to create another #tempTable within the same connection.

IF OBJECT_ID('tempdb..#tempTable') IS NULL
 CREATE TABLE #tempTable (Id int)

Unfortunately, the code you've shared is missing bits which makes it hard to provide a good recommendation. But understanding the cause of the error should be enough for you to come up with a solution.