RDO: Getting Data from Temp Tables Created by Stored Procedure (147938)

SUMMARY

It is common to have stored procedures use temporary tables to build a result set. When using RDO (Remote Data Object) to call this type of stored procedure it appears as though RDO does not return any results, only an empty result set and no errors.

Testing this same query with the Microsoft SQL Server version 6.0 ISQL utility produces the expected results, which leads you to believe RDO is restricted from reading these temporary tables.

This is not an RDO restriction and RDO CAN retrieve data from a temporary table created in a stored procedure. However, no data access library can create a SCROLLABLE CURSOR on a temporary table created within a stored procedure. Temporary tables that are created within a stored procedure are DESTROYED when that stored procedure ends. If the table is not there, no data access library can possibly open a scrollable cursor on it, and go back to it when the next rowset is needed or when the user tries to update, insert or delete it.

ISQL does not open a cursor, nor use a cursor library, so it just reads data off the pipe, one row at a time, read-only, forward-only (this is also the default mode of dblib so many people are used to working this way). The good news is that RDO can do this as well. You just need to tell SQL Server not to open a server-side cursor on the temporary table. This is done by setting the RowsetSize property to 1 and opening the result set as forward-only and read-only (just like ISQL does). RDO uses 100 as the default for RowsetSize, since this is the optimal setting for most cursor operations and when it is set to anything greater than 1, SQL Server' ODBC driver MUST attempt to create a server-side cursor, since fat rowsets require the ability to scroll and possibly update.

Remember that using cursors is much different than the read-only, forward-only, rowset size of 1, approach to getting data back to the client. To support the operations that cursors do (like scrolling backwards, doing positioned updates, etc.), the source of the data has to live for the entire time the cursor is open. RDO can operate in a cursor mode, or a "fire hose" mode, which allows you to perform whatever functionality you want.

MORE INFORMATION

Sample Program

Step 1: Create the Stored Procedure

This stored procedure simply creates a temporary table called "#temptest," filling it with all the rows from the Authors table in the Pubs database.

Open the SQL Server 6.0 ISQL utility and change the default database to Pubs.
Paste the following code into the Query window and click the Execute Query button, or press CTRL+E, to execute the Transact-SQL code:
```
   create proc TempTableTest as
   select * into #testtemp from authors
   select * from #testtemp

						
```
The following confirmation message displays

"This command did not return data, and it did not return any rows"

which indicates that the Transact-SQL code created the stored procedure correctly.

Step 2: Create the Visual Basic Code

The following Visual Basic code opens a connection to the server, creates a prepared statement for the stored procedure, sets the result set size to 1 and opens the result set as forward-only, read-only. When you run this code, all the author IDs and last names appear in the debug window.

This sample code uses a "DSN-less" ODBC connection so you do not need to set up a DSN (Data Source Name) with the ODBC Admin utility.

Start a new project in Visual Basic. Form1 is created by default.
Add a command button (Command1) and a listbox (list1) to Form1.

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

   Private Sub Command1_Click()
     Dim cn As rdoConnection
     Dim ps As rdoPreparedStatement
     Dim rs As rdoResultset
     Dim strConnect As String

     strConnect = "driver={SQL Server};server=myserver;" & _
       "database=pubs;uid=<username>;pwd=<strong password>"
     rdoEnvironments(0).CursorDriver = rdUseOdbc
     Set cn = rdoEnvironments(0).OpenConnection( _
       dsName:="", _
       Prompt:=rdDriverNoPrompt, _
       ReadOnly:=False, _
       Connect:=strConnect)

     Set ps = cn.CreatePreparedStatement( _
       Name:="ps1", _
       SqlString:="{call TempTableTest }")
     ps.RowsetSize = 1
     Set rs = ps.OpenResultset( _
       Type:=rdOpenForwardOnly, _
       LockType:=rdConcurReadOnly)

     While Not rs.EOF
       list1.AddItem rs(0) & ", " & rs(1)
       rs.MoveNext
     Wend
   End Sub

Note that you need to change your DATABASE, UID, and PWD parameters in the OpenConnection method.
Start the program or press the F5 key.
Click the Command1 button to execute the stored procedure and display the results in the list control.