PRB: ODBC Prepared Statement Errors with Temporary Tables (198428)
The information in this article applies to:
- Microsoft SQL Server 7.0
- Microsoft ODBC Driver for 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 '#<temp-table>'.
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.
Modification Type: | Major | Last Reviewed: | 11/21/2003 |
---|
Keywords: | kbBug kbpending kbprb KB198428 |
---|
|