Microsoft KB Archive/294141

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

Article ID: 294141

Article Last Modified on 5/8/2003

-

APPLIES TO


 * Microsoft Data Access Components 2.1
 * Microsoft Data Access Components 2.1 Service Pack 2
 * Microsoft Data Access Components 2.1 Service Pack 1
 * Microsoft Data Access Components 2.1 Service Pack 2
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.5 Service Pack 1
 * 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:

&quot;Non-Root Provider Commands must be rowset producing.&quot;

With the SQLOLEDB provider that ships with MDAC 2.5 and later:

&quot;Provider Command for child rowset does not produce a rowset.&quot;

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
 In Microsoft Visual Basic, create a new Standard EXE project. Form1 is created by default. Add a reference to Microsoft ActiveX Data Objects.  Paste the following code into the code section of Form1: Option Explicit

Const strConn = &quot;Provider=MSDataShape;Data Provider=SQLOLEDB;Persist Security Info=False;&quot; & _ &quot;Data Source=MySQLServer;Initial Catalog=Northwind;User Id=Me;Password=MyPassword&quot; Const strSQL = &quot;SHAPE AS Employees&quot; & _ &quot; APPEND ( AS Territory&quot; & _            &quot; RELATE 'EmployeeID' TO 'EmployeeID') AS EmpTerritory&quot;

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   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  Change the Data Source, User ID, and Password in the connection string (strConn). Run the code.</li></ol>

Keywords: kbdatabase kbprb KB294141

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.