DOCUMENT:Q280134 01-JAN-2001 [vbwin] TITLE :PRB: Error When You Create SQL Server TEMP Tables Using RDO PRODUCT :Microsoft Visual Basic for Windows PROD/VER::4.0,5.0,6.0,6.0 SP3,6.0 SP4,6.5,6.5 Service Pack 1 and later,7.0,7.0 Service Pack 1,7. OPER/SYS: KEYWORDS:kbRDO kbRDO100 _IK12473 kbSQLServ kbVBp kbVBp400 kbVBp500 kbVBp600 kbSQLServ650 kbSQLSe ====================================================================== ------------------------------------------------------------------------------- The information in this article applies to: - Microsoft Visual Basic Enterprise Edition for Windows, versions 4.0, 5.0, 6.0, 6.0 SP3, 6.0 SP4 - Microsoft SQL Server versions 6.5, 6.5 Service Pack 1 and later, 7.0, 7.0 Service Pack 1, 7.0 Service Pack 2 - Microsoft SQL Server 2000 (all editions) ------------------------------------------------------------------------------- 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 '#' 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: 1. In Visual Basic, create a new Standard EXE project. Form1 is created by default. 2. Set a reference to the Microsoft Remote Data Object library version 1.0 or 2.0. 3. In Form1, add a command button. 4. In the Click event procedure of the command button, paste the following code: Dim cn As rdoConnection Dim rs As rdoResultset Set cn = New rdoConnection cn.Connect = "DSN=;UID=sa;PWD=;" 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 5. In the RDO connection string, specify an ODBC DSN, a user ID, and a password to connect to a SQL Server database. 6. 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. 7. Stop the execution of the project. 8. 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 9. 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. Additional query words: RDO, TEMP table, rdoConnection, 40002 ====================================================================== Keywords : kbRDO kbRDO100 _IK12473 kbSQLServ kbVBp kbVBp400 kbVBp500 kbVBp600 kbSQLServ650 kbSQLServ700 kbGrpDSVBDB kbSQLServ2000 Technology : kbVBSearch kbSQLServSearch kbAudDeveloper kbZNotKeyword6 kbSQLServ700 kbSQLServ650 kbSQLServ2000Search kbZNotKeyword2 kbVB500Search kbVB600Search kbVB500 kbVB600 kbVB400Search kbVB400 kbSQLServ650SP1 kbSQLServ700SP1 kbSQLServ700SP2 kbSQLServ2000 kbVB600SP3 kbVB600SP4 Version : :4.0,5.0,6.0,6.0 SP3,6.0 SP4,6.5,6.5 Service Pack 1 and later,7.0,7.0 Service Pack 1,7.0 Service Pack 2 Issue type : kbprb ============================================================================= THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY. Copyright Microsoft Corporation 2001.