INF: Cannot SQLPrepare() the Creation of Temporary Objects

• Microsoft SQL Server, version 6.5

SUMMARY

If an ODBC application attempts to create a local temporary table or procedure in an SQL command issued using SQLPrepare() and SQLExecute(), subsequent references to the object might get errors that the object does not exist. This happens when the application or data source has specified that the driver should generate stored procedures to support SQLPrepare().

MORE INFORMATION

The Microsoft SQL Server ODBC driver can support the SQLPrepare() SQLExecute() model of ODBC by generating a stored procedure on the SQLPrepare() call and then executing that procedure when the application calls SQLExecute(). This is controlled by either setting driver specific SQLSetConnect() options as documented in the driver's documentation, or by setting on the "Generate Stored Procedure for Prepared Statement" option when defining the data source in the ODBC Administrator.

If an application with this option set on tries to create a local temporary table or stored procedure using SQLPrepare() and SQLExecute(), the object will not exist after the SQLExecute() command completes. For example, if an application does:

   SQLPrepare(hstmt,
      "create table #sometable(cola int, colb char(8))",
      SQL_NTS);
   SQLExecute(hstmt);

   SQLPrepare(hstmt,
      "select * from authors into #sometable",
      SQL_NTS);
   SQLExecute(hstmt);

This is a side effect of the driver using a stored procedure to do the work requested by the application. In the first example above, on the SQLPrepare() command the ODBC driver sends the following command to SQL Server:

   create procedure #ODBC#NNNNNNNN as
   create table #sometable(cola int, colb char(8))

   EXEC #ODBC#NNNNNNNN

ODBC Applications should use SQLExecDirect() to execute SQL commands creating local temporary tables or procedures. SQLPrepare() and SQLExecute() are speed optimizations for commands that will be executed repeatedly. If a command creating an object is executed multiple times, then all the executions after the first should fail with an error that the object already exists, therefore SQLExecDirect() should be all that is needed to execute an SQL command which creates an object.

Please note that this does not affect the creation of global temporary objects (objects whose name starts with ##). For example, if an application does:

   SQLPrepare(hstmt,
      "create table ##sometable(cola int, colb char(8))",
      SQL_NTS);
   SQLExecute(hstmt);