Microsoft KB Archive/155818

= INF: Cannot SQLPrepare the Creation of Temporary Objects =

Article ID: 155818

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q155818



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

or

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 KB155818

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.