PRB: Error Opening Shaped Recordset Based on Recordset with Temporary Table (294141)


The information in this article applies to:

  • Microsoft Data Access Components 2.1
  • Microsoft Data Access Components 2.1 (GA)
  • Microsoft Data Access Components 2.1 SP1
  • Microsoft Data Access Components 2.1 SP2
  • Microsoft Data Access Components 2.5
  • Microsoft Data Access Components 2.5 SP1
  • Microsoft Data Access Components 2.6
  • Microsoft Data Access Components 2.7
This article was previously published under Q294141

SYMPTOMS

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:
"Non-Root Provider Commands must be rowset producing."
With the SQLOLEDB provider that ships with MDAC 2.5 and later:
"Provider Command for child rowset does not produce a rowset."
With Open Database Connectivity (ODBC):
Invalid object name '#TempTable'.

CAUSE

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

STATUS

This behavior is by design.

MORE INFORMATION

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.
Modification Type:MajorLast Reviewed:5/8/2003
Keywords:kbDatabase kbprb KB294141
©2002 Microsoft Corporation. All rights reserved.