Problem: Recordset is closed after calling a stored procedure that uses temp tables

Problem: Recordset is closed after calling a stored procedure that uses temp tables

Post by Oren Hollande » Fri, 10 Nov 2000 04:00:00



I am using SQL server 2K, ADO 2.6.
When I call a stored procedure that has a SELECT clause from a temp table, the recordset returns closed.

BTW, the Query Analyzer shows the returned values just fine.

 
 
 

Problem: Recordset is closed after calling a stored procedure that uses temp tables

Post by Andrew J. Kell » Fri, 10 Nov 2000 04:00:00


More than likely the temp table was automatically closed at the end of the session that created it. Local temp tables are scoped to the session. If you create the table and do the select in one session you shouldn't have a problem.

--
Andrew J. Kelly
Targitmail.com


  I am using SQL server 2K, ADO 2.6.
  When I call a stored procedure that has a SELECT clause from a temp table, the recordset returns closed.

  BTW, the Query Analyzer shows the returned values just fine.

 
 
 

Problem: Recordset is closed after calling a stored procedure that uses temp tables

Post by Don Arsenaul » Fri, 10 Nov 2000 04:00:00


I had the problem of a closed recordset when the stored procedure was using
RPC to get results from another stored procedure on a remote SQL Server.  I
can't remember if I got it working by rewriting the stored procedures or by
upgrading to SQL7 SP2.

Here's a couple of other things to check.

There's some property/parameter that lets you create recordsets that does
not return any rows.  Make sure "returns rows" (can't think of it offhand)
is turned on for the execute/recordset.

Stored procedures can return multiple resultsets.  Make sure the stored
procedure does not have any extraneous SELECT statements or calls to other
stored procedures that return a resultset.  There's a property or method for
the recordset object that allows you to move from one resultset to the next.

Don.


I am using SQL server 2K, ADO 2.6.
When I call a stored procedure that has a SELECT clause from a temp table,
the recordset returns closed.

BTW, the Query Analyzer shows the returned values just fine.

 
 
 

Problem: Recordset is closed after calling a stored procedure that uses temp tables

Post by Frank Figear » Fri, 10 Nov 2000 04:00:00


Oren,

Try using SET NOCOUNT ON as the first statement of your stored procedure, and SET NOCOUNT OFF as the last. For more details, see KB article Q235340.
--
Frank Figearo - MCSD,MCT

  I am using SQL server 2K, ADO 2.6.
  When I call a stored procedure that has a SELECT clause from a temp table, the recordset returns closed.

  BTW, the Query Analyzer shows the returned values just fine.

 
 
 

1. Problem using shape recordsets with stored procedure that uses a temp table

Hello,

i use a stored procedure to get data. This sp uses a temporary table to get
the correct results. There is no problem with this sp retrieving the data
directly into an ado recordset, but if i want to use this sp in a shape
command the ODBC driver raises an error that he could not find the temporary
table.
In normal the temporary table is created in the sp and will be deleted after
executing them.

Is there anywhere a documentation on using temporary tables with a
hierarchical recordset, or better knows anybody the solution for this
problem.

The used shape command:
SHAPE {{ CALL sp_GetProductsByCategory (10,3,12) }} AS Products
APPEND ({{ CALL sp_GetMediaFiles() }} AS Mediafiles RELATE 'ProductID' TO
'ProductID') AS Mediafiles

The raised error:
Microsoft OLE DB Provider for ODBC Drivers: -2147467259
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
'#results'

Thanks a lot for reply
Ciao

2. help or tutorial link

3. T-SQL Survey

4. temp tables in called stored procedures...

5. TDS Buffer Stumper

6. Temp table in stored procedure called by DTS

7. JDBC and SQLserver

8. Returning a recordset from a temp table in a stored procedure into ASP

9. Opening recordset on stored procedure with temp tables

10. ADO recordsets and Stored procedures with temp tables

11. Calling Stored procedure using recordset object

12. Problem with retrieving an ADO Recordset from SQLServer using Temp Tables