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

• Microsoft Visual Basic Control Creation, Learning, Professional, and Enterprise Editions for Windows, version 5.0
• Standard, Professional, and Enterprise Editions of Microsoft Visual Basic, 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")

   ARTICLE-ID: Q147938
   TITLE     : 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'.
   

Keywords          : VB4ALL VB4WIN vb5all
Version           : 4.0 5.0
Platform          : NT WINDOWS
Issue type        : kbprb