PRB: Error When You Create SQL Server TEMP Tables Using Remote Data Objects (RDO) (280134)

SYMPTOMS

When you create a SQL Server local temporary table using the rdoConnection object by calling its .Execute method with default parameters, and then attempt to access the table after the Remote Data Objects (RDO) method has run, you may receive one of the following error messages:

Run-time error '40002': 37000:[Microsoft][ODBC SQL Server Driver][SQL Server] Statement(s)could not be prepared

-or-

Run-time error '40002': S0002:[Microsoft][ODBC SQL Server Driver][SQL Server] Invalid Object Name '#<Name of the temporary table>'

CAUSE

The creation and use of temporary database tables to facilitate the storage and manipulation of volatile intermediate data is a common programming practice. The default behavior of the SQL Server Open Database Connectivity (ODBC) driver is to create and use temporary stored procedures to run prepared statements. The .Execute method of the rdoConnection object uses the SQLPrepare() and SQLExecute() ODBC application programming interface (API) calls by default to run a SQL statement as a prepared statement. Temporary tables that are created by a stored procedure are automatically dropped when the procedure completes execution. As a result, when you attempt to access a SQL Server temporary table that was created by calling the .Execute method of an rdoConnection object with default parameters, in subsequent statements you receive one of the error messages specified in the "Symptoms" section.

RESOLUTION

To resolve this problem, use one of the following workarounds:

Specify rdExecDirect as the Options parameter of the .Execute method of the rdoConnection object to bypass the creation of a temporary stored procedure, and force the usage of the SQLExecDirect() ODBC API function to run the SQL query that creates the temporary table.
If you use a SQL Server version 6.5 database, you can turn off the option to use temporary stored procedures to run prepared statements in the ODBC data source name (DSN) configuration properties. However, you cannot turn off this property when you create an ODBC DSN to connect to a SQL Server 7.0 or SQL Server 2000 database. It is turned on by default and the check box to control its setting in the DSN configuration dialog box is dimmed.
Use an rdoQuery object to run the SQL query that creates the temporary table after it sets its Prepared property to True.

MORE INFORMATION

Steps to Reproduce Problem

To set up and test a code sample that reproduces the problem, follow these steps:

In Visual Basic, create a new Standard EXE project. Form1 is created by default.
Set a reference to the Microsoft Remote Data Object library version 1.0 or 2.0.
In Form1, add a command button.
In the Click event procedure of the command button, paste the following code.

Note You must change UID=<username> and PWD =<strong password> to the correct values before you run this code. Make sure that UID has the appropriate permissions to perform this operation on the database.

Dim cn As rdoConnection
Dim rs As rdoResultset

Set cn = New rdoConnection

cn.Connect = "DSN=<ODBC DSN>;UID=<username>;PWD=<strong password>;"
cn.EstablishConnection

cn.Execute "Create table #temptest(mid integer, mname varchar(20))" 
cn.Execute "Insert into #temptest values(1,'Jack')"

Set rs = cn.OpenResultset("Select * from #temptest")
Do While Not rs.EOF
 Debug.Print rs.rdoColumns("mname")
 rs.MoveNext
Loop
rs.Close

cn.Close
Set cn = Nothing

In the RDO connection string, specify an ODBC DSN, a user ID, and a password to connect to a SQL Server database.
Run the project. When the form is displayed, click the command button. The cn.Execute statement attempts to add a row to the #temptest temporary table, and you receive one of the runtime errors specified in the "Symptoms" section.
Stop the execution of the project.
Modify the code to specify rdExecDirect as the Options parameter in the cn.Execute statement that creates the temporary table. The modified statement should resemble the following:
```
cn.Execute "Create table #temptest(mid integer, mname varchar(20))", rdExecDirect
					
```
Run the project. When the form is displayed, click the command button. You do not receive the runtime error. The temporary table is created, and the subsequent statements that access it complete successfully.