Re: Stored Procedure returning a closed recordset

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: William \(Bill\) Vaughn (billvaRemoveThis_at_nwlink.com)
Date: 02/11/05 

Date: Fri, 11 Feb 2005 04:04:52 -0800

This behavior is by design. Each TSQL operation creates a resultset. ADO
processes these resultsets one-at-a-time. Those that contain rowsets (the
result of a SELECT) cause the State flag to be set to adStateOpen--those
that don't State is set to adStateClosed (as you're seeing). You have at
least two choices when processing resultsets returned from stored
procedures:
    1) Add SET NOCOUNT ON to eliminate all non-rowset resultsets. This
throws away the number of rows affected by the action command (INSERT etc.).
    2) Step through the resultsets with the NextRecordset method until the
returned Recordset is Nothing.

More details and examples? Check out my ADO Examples and Best Practices
book.
hth 

-- 
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
<kaldvelski@yahoo.co.uk> wrote in message 
news:1108121156.510676.97220@f14g2000cwb.googlegroups.com...
> I'm executing a stored procedure (on SQL server) that should return a
> series of records. However, I'm getting a closed recordset as if ADO
> doesn't think the sproc returns any records - when it should be.
>
> After stripping the sproc down I discovered that if the sproc is a
> simple SELECT then it returns fine but if I perform an INSERT before
> the SELECT then I get nothing back. Executing the stored procedure in
> Query Analyzer is successful and returns records so it must be ADO's
> problem.
>
> Any ideas what ADO's problem is?
>
> Thanks in advance
>
> Craig
>
> btw I'm using ADO 2.7 with VB6 against SQL Server 2000
>

Relevant Pages

  • Re: no records returned from stored procedure based command when working on local temp tables
    ... Vince C. ... > Each Resultset might contain a rowset. ... > Resultsets you're seeing are from the operations executed early in the SP ... > for your version of ADO. ...
    (microsoft.public.data.ado)
  • Stored Procedure returning a closed recordset
    ... I'm executing a stored procedure that should return a ... I'm getting a closed recordset as if ADO ... doesn't think the sproc returns any records - when it should be. ...
    (microsoft.public.data.ado)
  • Re: DataSet w/ many tables to one Datagrid
    ... > I have a dataset that calls a stored procedure within our system. ... If all the resultsets have the same number of columns, ... Bob Barrows ... Please reply to the newsgroup. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Multiple ResultSets
    ... I have a problem again while retriveing multiple results sets from SQLServer ... later im trying to retrive multiple resultsets one after other. ... But im getting rs1 and rs1 are null. ... >> I have a stored procedure which returns mulitple resultsets ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: Using Subqueries in an UPDATE Statement
    ... more) resultsets and not a table or a view. ... Sylvain Lafontaine, ing. ... I have the following stored procedure and i would like to know how I ... I want to insert InvoiceAmount into a table called tbl_Case (field ...
    (microsoft.public.access.adp.sqlserver)