INF: Cannot SQLPrepare() the Creation of Temporary Objects

ID: Q155818

The information in this article applies to:

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

Subsequent references to #sometable will fail with object not found errors.

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

where NNNNNNNN is a string of numbers that will make the procedure name unique. On the SQLExecute() command the driver sends the following to SQL Server:


   EXEC #ODBC#NNNNNNNN

As per the "Referencing Objects" section for the CREATE PROCEDURE command in the Transact-SQL Reference, local temporary objects created in a stored procedure are automatically dropped when the procedure exits.

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

then ##sometable will exist after the SQLExecute() completes. As per the CREATE PROCEDURE section of the Transact-SQL Reference, global temporary objects created in a procedure will still exist when the procedure terminates.

Keywords : kbnetwork SSrvStProc
Version : 2.65.0201
Platform : WINDOWS
Issue type :