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