Temporary tables in SQL Server 2000

[Brian Smith <BSmith@TEMPO.CO.UK>]

When setting up an ODBC connection to a Microsoft SQL Server 2000 database,
the option "Create temporary stored procedures for prepared SQL statements
and drop the stored procedures" is forced ON and is not able to be cleared.
This has the result that an SQR report which uses any temporary tables
(CREATE TABLE ##temp in the begin-setup section) does not work, since the
#temp table was created inside a stored procedure, and thus ceases to exist
when the procedure ends.

SQR then gives an error such as:
(SQR 5528) ODBC SQL dbexec: SQLExecute error 208 in cursor 11:
   [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
'#temp'.
(SQR 5528) ODBC SQL dbexec: SQLExecute error 8180 in cursor 11:
   [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be
prepared.

Error on line 125:
   (SQR 3735) Could not execute SQL.

This is from numerous reports that work perfectly when targetting a SQL
Server 6.5 database.
Unfortunately the -XP command-line option is not available under Windows.

Is there a fix for this (without changing the report)?


Brian Smith


***IMPORTANT***
The information in this mail is confidential and is intended solely for the
addressee. Access, copying or re-use of information in it by anyone else is
unauthorised. Any views or opinions presented are solely those of the author
and do not necessarily represent those of Tempo Ltd or any of its
affiliates.