I want to create a temporary table on SQL Server 2012 and insert into it in bulk. I don't always know the type of parameters that the temp table will be created with, so I need to invoke SQLDescribeParam so that I can bind the parameters. I am using the following stripped down code after copying the sample code for SQLDescribeParam from http://msdn.microsoft.com/en-us/library/ms710188(v=vs.85).aspx
The code below works perfectly fine for the "SQL Server Native Client 10.0" ODBC driver against SQL Server 2012, and I am able to bind parameters and insert into the temp table. But the SQLDescribeParam call fails for "SQL Server Native Client 11.0" driver with the following two errors:
Error record 1:
Description: '[Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid object name '#myTemp'.'
SQL State: 42S02
Native Error: 208
Error record 2:
Description: '[Microsoft][SQL Server Native Client 11.0][SQL Server]The batch could not be analyzed because of compile errors.'
SQL State: 42000
Native Error: 11501
What do I need to do to get SQLDescribeParam to behave on "SQL Server Native Client 11.0"?
I am using the 64 bit "SQL Server Native Client" driver on Windows 7.
The code is below:
SQLSMALLINT NumParams, i, DataType, DecimalDigits, Nullable;
SQLULEN ParamSize;
SQLWCHAR strCreateQuery[] = L"CREATE TABLE [#myTemp] ( mycol INT)";
SQLWCHAR strInsertQuery[] = L"INSERT INTO [#myTemp] ([mycol]) VALUES (?)";
SQLHSTMT hstmt;
{ // Create temp table #myTemp
// Allocate hstmt
SQLExecDirectW( hstmt, strCreateQuery, SQL_NTS );
// Deallocate hstmt
}
// Allocate hstmt
SQLPrepare(hstmt, strInsertQuery, SQL_NTS);
SQLNumParams(hstmt, &NumParams);
if (NumParams) {
for (i = 0; i < NumParams; i++) {
// Describe the parameter.
SQLDescribeParam(hstmt, i + 1, &DataType, &ParamSize, &DecimalDigits, &Nullable) );
}
}
// Deallocate hstmt