Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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


View products that this article applies to.

This article was previously published under Q294141

↑ Back to the top


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'.

↑ Back to the top


Cause

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

↑ Back to the top


Status

This behavior is by design.

↑ Back to the top


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.

↑ Back to the top


Keywords: KB294141, kbprb, kbdatabase

↑ Back to the top

Article Info
Article ID : 294141
Revision : 3
Created on : 5/8/2003
Published on : 5/8/2003
Exists online : False
Views : 447