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.