Microsoft KB Archive/190124

= PRB: Can Not Create temp Table Using ODBCdirect =

Article ID: 190124

Article Last Modified on 11/7/2003

-

APPLIES TO


 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition

-



This article was previously published under Q190124



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.

Note You must change UID and PWD to the correct values before you run the following code snippets. Make sure that UID has the appropriate permissions to perform this operation on the database.  Create an ODBC DSN called  with the ODBC32 administrator. Open Visual Basic and create a new Standard.exe project. On the Project menu, click References, and select the Microsoft DAO 3.5x Object Library.  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= ;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   Start ISQL_W and run the following statement: SELECT * FROM TestTemp DROP TABLE TestTemp </li></ol>

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 <DROP TABLE> 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 <dbExecDirect> 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 <Form-Load> 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= ;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 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.

<div class="status_section">

STATUS
This behavior is by design.

<div class="moreinformation_section">

Steps to Reproduce Behavior
<ol> Create an ODBC DSN called <Pubs> with the ODBC32 administrator.</li> Open Visual Basic and create a new Standard.exe project.</li> On the Project menu, click References, and select the Microsoft DAO 3.5x Object Library.</li>  In the <Form-Load> event, paste the following code.

Note You must change UID and PWD 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 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= ;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 </li></ol>

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.

<div class="references_section">