Error: Invalid object name : BCP copy out failed

Question

Hi .

I am using BCP command in my .NET code to retrieve data from SQL table and move it to a delimited file.

The query work fine most of the time, however, when the records are more - I am getting error as :

Starting copy...
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name '##transaction_changes'.
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 17 for SQL Server]Unable to resolve column level collations

BCP copy out failed

Below is how the query is defined with the .net code :

 string transactionchangesExist = "SELECT count(1) FROM tempdb.sys.tables WHERE [name] = '##transaction_changes'";

            transaction_changes = "create TABLE session.##transaction_changes( col1 char(15) not null, col2 int not null) ";

            inserttransaction_changes = "insert into session.##transaction_changes select col1, col2 from tablename with (nolock) 
                                          where CAST(TSTAMP as date) >= '10/10/2020' and
                                                CAST(TSTAMP as date) <= '10/10/2021' ";          

                ADOHelper dbhelper = new ADOHelper(connstr);

                if (strQuery.ToLower().Contains("session.##transaction_changes"))
                {
                    int transactionchangescount = dbhelper.ExecScalarCount(transactionchangesExist);

                    if (transactionchangescount == 0)
                    {
                        //table is not there .  create

                        dbhelper.ExecNonQuery(transaction_changes, null);

                        dbhelper.ExecNonQuery(inserttransaction_changes, null);                    
                    }
                    else
                    {
                        // "Temp Table session.##transaction_changes Exist";
                    }
                }

// BCP Query
SELECT a.col1, a.col2, a.col3 FROM Table2 a WITH (NOLOCK) 
inner join session.##transaction_changes b WITH (NOLOCK) on a.col1 = ( b.col1 COLLATE SQL_Latin1_General_CP1_CI_AS) " queryout "filepath"  -S servername -d dbname -b 1000 -c -C 65001 -t~

How to fix this ? Error is : [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name '##transaction_changes'.

This error does not occur everytime.

Thanks

Answer 1

Global temporary tables are difficult to use. A global temp table is visible to all processes. It exists as long as the process that created it is alive, but when this process exists, the table goes away. Or, well, the documentation says that the table will remain in existences as long as there are processes referring to it. But what that really means, is not clear. I would assume that if there is a query running when the creator process exists, that query will complete. But as soon as that query completes, the table goes away.

It sounds like something like this is happening here. I think you should do a redesign use a different approach. For instance a permanent table which is keyed by a guid which you pass around as key. (This permits multiple processes to be running in parallel.)