ExecuteScalar on temp table throwing exception

Lance James 366 Reputation points
2022-03-21T15:24:29.77+00:00

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:
185236-image.png

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

C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,408 questions
{count} votes

Accepted answer
  1. AgaveJoe 26,246 Reputation points
    2022-03-21T15:46:31.27+00:00

    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.


0 additional answers

Sort by: Most helpful