Suppressing second recordset from stored procedure

[A_X_L_V]

Hi,

I've ran into a small dilemma.
I use ADO to get Recordset from Stored Procedure.
We use generic function written in VBScript to retrieve last Recordset
returned by it.
One of our stored procedures calls another stored procedure, which also
returns a recordset.
That causes a problem, because now ADO returns 2 recordsets instead of 1,
and our generic data access function fails.

I'd like to find a way of suppressing all recordsets except one inside
stored procedure.
I can't change VBscriptc function, because it's used by more than one
application.

Just a thought, I've tried SET NOCOUNT, and it doesn't work in the above
situation.

Thanks
Alex

[Bob Barrows]

A_X_L_V wrote:

> Hi,
>
> I've ran into a small dilemma.
> I use ADO to get Recordset from Stored Procedure.
> We use generic function written in VBScript to retrieve last Recordset
> returned by it.
> One of our stored procedures calls another stored procedure, which
> also returns a recordset.
> That causes a problem, because now ADO returns 2 recordsets instead
> of 1, and our generic data access function fails.
>
> I'd like to find a way of suppressing all recordsets except one inside
> stored procedure.
> I can't change VBscriptc function, because it's used by more than one
> application.
>
> Just a thought, I've tried SET NOCOUNT, and it doesn't work in the
> above situation.
>
> Thanks
> Alex

You cannot "suppress" recordsets returned by a procedure. You COULD revise
the vbscript code so that the "wrong" recordset is ignored, but you say you
want to avoid this, leaving only one alternative: you will have to rewrite
the procedure so it only returns a single resultset

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[A_X_L_V]

Bob, I can't rewrite the procedure...I have to call the other stored
procedure inside mine,
because it performs business logic. That second proc returns a recordset,
which I don't use
inside my main stored proc other than checking for status code. My main
stored procedure also
has to return a recordset, which makes it 2.

So what you are saying is that I can't tell SQL Server "I just need the last
recordset", similar to what SET NOCOUNT does?

Of course, I can, for instance, split my current proc into 2, call proc_1,
then that other proc I am calling in the middle,
and then proc_2, but this could be a bit of a maintenance problem in the
future

Thanks


"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:%23dM8FY%23oDHA.3688@TK2MSFTNGP11.phx.gbl...

> A_X_L_V wrote:

> > Hi,
> >
> > I've ran into a small dilemma.
> > I use ADO to get Recordset from Stored Procedure.
> > We use generic function written in VBScript to retrieve last Recordset
> > returned by it.
> > One of our stored procedures calls another stored procedure, which
> > also returns a recordset.
> > That causes a problem, because now ADO returns 2 recordsets instead
> > of 1, and our generic data access function fails.
> >
> > I'd like to find a way of suppressing all recordsets except one inside
> > stored procedure.
> > I can't change VBscriptc function, because it's used by more than one
> > application.
> >
> > Just a thought, I've tried SET NOCOUNT, and it doesn't work in the
> > above situation.
> >
> > Thanks
> > Alex

>
> You cannot "suppress" recordsets returned by a procedure. You COULD revise
> the vbscript code so that the "wrong" recordset is ignored, but you say

you

> want to avoid this, leaving only one alternative: you will have to rewrite
> the procedure so it only returns a single resultset
>
> Bob Barrows
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

[Aaron Bertrand [MVP]]

> So what you are saying is that I can't tell SQL Server "I just need the
last

> recordset", similar to what SET NOCOUNT does?

No.

Your only option, it seems, is to let VBScript ignore (using
rs.nextRecordset()) the set(s) you don't care about.

Or to rewrite the stored procedure (e.g. get past whatever it is that makes
it so that you "can't")

[Aaron Bertrand [MVP]]

> I can't change VBscriptc function, because it's used by more than one

> application.

You can't change the VBScript, you can't change the stored procedure, and
you can't change how SQL Server works. Something has to give here. Your
requirements make your job impossible to do; you're going to have to go back
to whoever is dictating that you can't change any of these things, and tell
them that either you are allowed to change them, write similar versions of
them for this specific use, or tell them to piss off.

> Just a thought, I've tried SET NOCOUNT,

What ever made you think that would have any impact on the resultsets that
actually return valid data?

[Keith]

>-----Original Message-----

>> So what you are saying is that I can't tell SQL

Server "I just need the

>last

>> recordset", similar to what SET NOCOUNT does?

>
>No.
>
>Your only option, it seems, is to let VBScript ignore

(using

>rs.nextRecordset()) the set(s) you don't care about.
>
>Or to rewrite the stored procedure (e.g. get past

whatever it is that makes

>it so that you "can't")
>
>
>.
>

I'm just winging this but:

The only thing you can change is the SP that calls the
second one, correct?
You might try to capture the recordset from the secondary
SP in a CURSOR, then deallocate the CURSOR, and then
return your own recordset.

Just a thought, haven't tried it.