1

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
user2237963
  • 103
  • 7

0 Answers0