Microsoft KB Archive/198428

= PRB: ODBC Prepared Statement Errors with Temporary Tables =

Article ID: 198428

Article Last Modified on 11/21/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft ODBC Driver for Microsoft SQL Server 3.7

-



This article was previously published under Q198428



SYMPTOMS
When attempting to select data from a temporary table that was created by using ODBC prepared statements, the driver reports that the temporary table being queried does not exist. The error message reported is:

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#'.



CAUSE
SQL Server 7.0 Books Online states the following in the "Prepared Execution" topic which explains why temporary tables created with SQLPrepare may experience this problem:

  Prepared statements cannot be used to create temporary objects on SQL Server 7.0, or on earlier versions of SQL Server if the option to  generate stored procedures is active. With this option turned on, the prepared statement is built into a temporary stored procedure that is  executed when SQLExecute is called. Any temporary object created during the execution of a stored procedure is automatically dropped when the procedure finishes.

You may also see this problem when creating the temporary table using SQLExecDirect as the sp_executesql procedure may be used if the query is parameterized.



WORKAROUND
To work around this problem, try one of the following:
 * Create the temporary table without using the SQLPrepare API. Use the SQLExecDirect API to execute a non-parameterized SQL statement.
 * Use the odbccmpt utility to enable the 6.5 ODBC compatability option for the application and disable the Generate temporary stored procedures for prepared statements option. See the "odbccmpt Utility" topic in SQL Server 7.0 Books Online for more information.
 * Use version 3.60.0319 of the SQL Server driver with the Generate temporary stored procedures for prepared statements option disabled.



MORE INFORMATION
The following is a synopsis of how SQLPrepare / SQLExecute and SQLExecDirect calls are handled against SQL Server 6.x and SQL Server 7.0.

SQL Server 6.x:
All calls to SQLExecDirect will be executed as an SQL batch on the server.

If the Generate Stored Procedures for Prepared Statements option is turned on, calls to SQLPrepare will generate a temporary stored procedure in tempdb. Calls to SQLExecute will execute the stored procedure with the appropriate parameters.

If the Generate Stored Procedures for Prepared Statements option is turned off, SQLPrepare statements will be executed as an SQL batch on the server.

For more information on SQL Server 6.x handling of this situation, please see the following article in the Microsoft Knowledge Base:

155818 - INF: Cannot SQLPrepare the Creation of Temporary Objects

SQL Server 7.0:
There is no option to disable the prepare\execute model when using the ODBC 3.7 SQL Server driver against SQL Server 7.0. Any calls to SQLPrepare and SQLExecute will be executed internally by SQL Server using the prepare\execute model.

Parameterized calls to SQLExecDirect use the sp_executesql stored procedure. See the "Using sp_executesql" and "sp_executesql (T-SQL)" topics in SQL Server 7.0 Books Online for more information on this procedure.

Non-parameterized calls to SQLExecDirect will be executed as an SQL batch on the server.

Additional query words: open database connectivity temp msg err

Keywords: kbbug kbprb kbpending KB198428

-

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

© Microsoft Corporation. All rights reserved.