DOCUMENT:Q190124 11-AUG-1998 [vbwin] TITLE :PRB: Can Not Create temp Table Using ODBCdirect PRODUCT :Microsoft Visual Basic for Windows PROD/VER:WINDOWS:5.0,6.0 OPER/SYS:WINDOWS KEYWORDS: ====================================================================== --------------------------------------------------------------------- The information in this article applies to: - Microsoft Visual Basic Enterprise Edition for Windows, versions 5.0, 6.0 --------------------------------------------------------------------- SYMPTOMS ======== When attempting to create a temporary table(for example, #temp) with ODBCdirect, you receive no indication that the temporary table was created and no errors are reported. CAUSE ===== The temporary table is created but as a result of its scope, the table appears not to be created. The default execution option for Connection and QueryDef statements is to run as a prepared statement. When the statement is executed as a prepared statement, the SQLPrepare ODBC API function is called and a temporary stored procedure is created in which the statement executes. As a result of the temporary stored procedure execution, the temporary table is destroyed as the stored procedure terminates. Thus, the scope of local temporary tables is the transaction space or stored procedure in which they were created. SQLServer Books Online contains a detailed discussion of the scopy of local and global temporary tables. To reproduce the problem, refer to the code sample contained in the MORE INFORMATION section of this article. RESOLUTION ========== If you need to refer to a temporary table created with ODBCdirect, send the SQL statement in one transaction statement or use the option with ODBCDirect Connection and QueryDef objects. For example: 1. Create an ODBC DSN called with the ODBC32 administrator. 2. Open Visual Basic and create a new Standard.exe project. 3. On the Project menu, click References, and select the Microsoft DAO 3.5x Object Library. 4. In the event, paste and run the following code: Dim ws As Workspace Dim cn As Connection Dim strConnect as string Dim strSQL as string Set ws = DBEngine.CreateWorkspace("", "", "", dbUseODBC) Set cn = ws.OpenConnection("", , , "ODBC;DSN=Pubs;Database=Pubs;UID=sa;PWD=") strSQL = "CREATE TABLE #test (field1 VARCHAR(20) NULL)" & vbCrLf & _ "CREATE TABLE TestTemp(field1 VARCHAR(20) NULL)" & vbCrLf & _ "INSERT INTO #test(field1) SELECT fname FROM Employee" & vbCrLf & _ "INSERT INTO TestTemp(field1) SELECT field1 FROM #test" cn.Execute strSQL 5. Start ISQL_W and run the following statement: SELECT * FROM TestTemp DROP TABLE TestTemp RESULTS: You should see rows returned in the results windows. If the #temp table was not created in step 4, TestTemp would not exist. This example demonstrates that the #temp table is created during step 4 but pausing/stopping the code at any point does not reveal that fact. The SQL statement is being executed and the connection is closed immediately, so you do not see the details within ISQL_W or Visual Basic. NOTE: The statement deletes the table from the database so you may run the example again. To refer to the temporary table at some other point in the code, use the option with the Connection or QueryDef object as in the following example. Follow steps 1 through 3 as in the previous example. Then paste the following code in the event and run the project: Dim ws As Workspace Dim cn As Connection Dim strConnect as string Dim strSQL as string Set ws = DBEngine.CreateWorkspace("", "", "", dbUseODBC) Set cn = ws.OpenConnection("", , , "ODBC;DSN=Pubs;Database=Pubs;UID=sa;PWD=") strSQL = "CREATE TABLE #test (field1 VARCHAR(20) NULL)" & vbCrLf & _ "INSERT INTO #test(field1) SELECT fname FROM Employee" cn.Execute strSQL, dbExecDirect strSQL = "CREATE TABLE TestTemp(field1 VARCHAR(20) NULL)" & vbCrLf & _ "INSERT INTO TestTemp(field1) SELECT field1 FROM #test" cn.Execute strSQL, dbExecDirect Start ISQL_W and run the following statement: SELECT * FROM TestTemp DROP TABLE TestTemp You should see rows returned in the results windows. This demonstrates that the temporary table was created and its scope was not limited to a prepared statement (temporary stored procedure) but the database connection. STATUS ====== This behavior is by design. MORE INFORMATION ================ Steps to Reproduce Behavior --------------------------- 1. Create an ODBC DSN called with the ODBC32 administrator. 2. Open Visual Basic and create a new Standard.exe project. 3. On the Project menu, click References, and select the Microsoft DAO 3.5x Object Library. 4. In the event, paste the following code: Dim ws As Workspace Dim cn As Connection Dim strConnect as string Dim strSQL as string Set ws = DBEngine.CreateWorkspace("", "", "", dbUseODBC) Set cn = ws.OpenConnection("", , , "ODBC;DSN=Pubs;Database=Pubs;UID=sa;PWD=") strSQL = "CREATE TABLE #test (field1 VARCHAR(20) NULL)" cn.Execute strSQL 'This statement results in an error. strSQL = "INSERT INTO #test(field1) SELECT fname FROM Employee" cn.Execute strSQL The temporary table was created but in a prepared statement (temporary stored procedure). Consequently, the temporary table was destroyed when the stored procedure terminated, resulting in an error on the subsequent reference to the temporary table. REFERENCES ========== SQLServer Books Online Visual Basic Books Online, "ODBCDirect Prepared" Additional query words: kbDSupport kbdse kbvbp600 kbvbp500 kbDAO350 kbDAO kbSQLServ kbDatabase ====================================================================== Version : WINDOWS:5.0,6.0 Platform : WINDOWS 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 1998.