PRB: Can't Read Temporary Table Created Using # Sign in RDO

• Microsoft Visual Basic Learning, Professional, and Enterprise Editions for Windows, version 6.0
• Microsoft Visual Basic Control Creation, Learning, Professional, and Enterprise Editions for Windows, version 5.0
• Microsoft Visual Basic Standard, Professional, and Enterprise Editions, 16-bit and 32-bit, for Windows, version 4.0

SUMMARY

When using Remote Data Object (RDO) to create a temporary table with a single pound sign (#) in the SQL statement, it appears that the temporary table is not created in SQL server. When an attempt is made to open this table within the same connection, RDO returns a run-time error.

CAUSE

When an action query is prepared in RDO and passed to SQL server, a stored procedure is created. However, this stored procedure is dropped after it is executed. Therefore, temporary tables created within the action query are destroyed once that stored procedure ends.

RESOLUTION

To work around this behavior, create a global temporary object by using a double pound sign (##). For example:

   rdoConn.Execute "Select * Into ##Temp1 From Authors"
        Set rs = rdoConn.OpenResultset("Select * from ##Temp1") 

Q147938 RDO: Getting Data from Temp Tables Created by Stored Procedure

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

1. Start a new project in Visual Basic. Form1 is created by default.



2. Add a CommandButton, Command1 to Form1.



3. Paste the following code into the General Declarations section of Form1:

   
            Private Sub Command1_Click()
   
         Dim rdoEnv As rdoEnvironment
         Dim rdoConn As rdoConnection
         Dim strConn As String
         Dim ps As rdoPreparedStatement
   
         Set rdoEnv = rdoEngine.rdoEnvironments(0)
            rdoEnv.CursorDriver = rdUseOdbc
   
            '***You need to change the SERVER, UID, and PWD parameters here.
         strConn = "driver={SQL Server};server=myserver;" & _
         "database=pubs;uid=sa;pwd="
            Set rdoConn = rdoEnv.OpenConnection( _
              dsName:="", _
              Prompt:=rdDriverNoPrompt, _
              ReadOnly:=False, _
              Connect:=strConn)
   
         rdoConn.Execute "Select * into #Temp1 From Authors"
              Set rs = rdoConn.OpenResultset("Select * from #Temp1")
              Do Until rs.EOF
                 Debug.Print rs.rdoColumns(0) & ", " & rs.rdoColumns(1)
                 rs.MoveNext
              Loop
              rs.Close
              rdoConn.Close
              rdoEnv.Close
   
            End Sub 




4. Make sure you have an appropriate ODBC data source, user ID, and password. Start the program by pressing the F5 key.



5. Click the Command1 button to execute the code. You will receive the following error message indicating that table #Temp1 in SQL server cannot be found:

   Run-time Error '400002':
   S0002: [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
   '#Temp1'.

Additional query words: kbVBp400 kbVBp500 kbVBp600 kbdse kbDSupport kbVBp kbRDO

Keywords : kbGrpVBDB
Version :
Platform : NT WINDOWS
Issue type : kbprb