question

PadmanabhanVenkatesh-6789 avatar image
0 Votes"
PadmanabhanVenkatesh-6789 asked JackJJun-MSFT commented

Error: Invalid object name : BCP copy out failed

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

sql-server-generaldotnet-csharp
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@PadmanabhanVenkatesh-6789, Welcome to Microsoft Q&A, could you provide the related code about the ADOHelper class? Also I want to know if your BCP query works well in the ODBC Driver 17. What is your current app and the related .NET framework?

0 Votes 0 ·

Hi.
Thanks for the reply. Please find the below code. This code is used in a console application

 public int ExecNonQuery(string qry, params object[] args)
         {
             using (SqlCommand cmd = CreateCommand(qry, CommandType.Text, args))
             {
                 cmd.CommandTimeout = 0;
                 return cmd.ExecuteNonQuery();
             }
         }
    
  public SqlCommand CreateCommand(string qry, CommandType type, params object[] args)
         {
             SqlCommand cmd = new SqlCommand(qry, _conn);
             cmd.CommandTimeout = 0;
         
             cmd.CommandType = type;
             if (args != null)
             {
                 // Construct SQL parameters
                 for (int i = 0; i < args.Length; i++)
                 {
                     if (args[i] is string && i < (args.Length - 1))
                     {
                         SqlParameter parm = new SqlParameter();
                         parm.ParameterName = (string)args[i];
                         parm.Value = args[++i];
                         cmd.Parameters.Add(parm);
                     }
                     else if (args[i] is SqlParameter)
                     {
                         cmd.Parameters.Add((SqlParameter)args[i]);
                     }
                     else throw new ArgumentException("Invalid number or type of arguments supplied");
                 }
             }
             return cmd;
         }
0 Votes 0 ·
JackJJun-MSFT avatar image JackJJun-MSFT PadmanabhanVenkatesh-6789 ·

@PadmanabhanVenkatesh-6789 , Thanks for the feedback, Based on my further test, I find that I still need to confirm something with you.

About

  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' ";         

Could you provide the data about tablename?


About 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)

What is the information about Table2 and SQL_Latin1_General_CP1_CI_AS?

By the way, If you run the BCP Query in the SSMS query directly, are you still getting the errors you mentioned? Or have you considered change the session.##transaction_changes to the normal name such as TransactionChanges?

If you tell me these information, it will be better for me to solve your problem.




0 Votes 0 ·

1 Answer

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.)

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.