PRB: Error Opening Shaped Recordset Based on Recordset with Temporary Table

When you open a shaped recordset, you may receive one of the following error messages:

With the Microsoft SQL Server OLE DB Provider (SQLOLEDB) that ships with MDAC versions earlier than 2.5: With the SQLOLEDB provider that ships with MDAC 2.5 and later: With Open Database Connectivity (ODBC):

This occurs if the child recordset is based on a recordset that uses a temporary table.

This behavior is by design.

The shape provider attempts to determine the child recordset's column information by calling the stored procedure with FMTONLY set to ON. In this case, the provider is not able to obtain enough information on the columns.

Steps to Reproduce Behavior

1. In Microsoft Visual Basic, create a new Standard EXE project. Form1 is created by default.
2. Add a reference to Microsoft ActiveX Data Objects.
3. Paste the following code into the code section of Form1:

   Option Explicit
   
   Const strConn = "Provider=MSDataShape;Data Provider=SQLOLEDB;Persist Security Info=False;" & _
           "Data Source=MySQLServer;Initial Catalog=Northwind;User Id=Me;Password=MyPassword"
   Const strSQL = "SHAPE {{SELECT EmployeeID,FirstName,LastName FROM Employees}} AS Employees" & _
                " APPEND ({{CALL sp_TestTempTable}} AS Territory" & _
                " RELATE 'EmployeeID' TO 'EmployeeID') AS EmpTerritory"
   
   Private Sub Form_Load()
       Dim cn As ADODB.Connection
       Set cn = New ADODB.Connection
       cn.Open strConn
       
       Dim rs As ADODB.Recordset
       Set rs = New ADODB.Recordset
       With rs
           .CursorLocation = adUseClient
           .LockType = adLockOptimistic
           
           .Open strSQL, cn
           
           Debug.Print .Fields(0)
           .Close
       End With
       
       Set rs = Nothing
       cn.Close
       Set cn = Nothing
   End Sub
   					

4. Create a stored procedure in your Microsoft SQL Server with the following T-SQL script:

   CREATE PROCEDURE sp_TestTempTable  AS
   
   SELECT EmployeeID, TerritoryDescription 
   INTO #TempTable
   FROM EmployeeTerritories AS E, Territories AS T
   WHERE E.TerritoryID = T.TerritoryID
   
   SELECT * FROM #TempTable
   					

5. Change the Data Source, User ID, and Password in the connection string (strConn).
6. Run the code.