Re: Missing Resultset When Calling an Stored Procedure w/a Nested

From: PKNET (PKNET_at_discussions.microsoft.com)
Date: 08/14/04 

Date: Sat, 14 Aug 2004 08:47:02 -0700

Yes, we tried advancing to the next recordset by using the NextRecordSet
method.

Besides attempting to use our own application to verify the problem, we used
a PocketQuery program written test program for the CEfusion product. The
PocketQuery program is a Query Analzyer for WinCE devices. Our stored
procedure call resulted in the same missing resultset when called from the
PocketQuery program on the WinCE device. In other words, the PocketQuery
program only returned the nested stored procedure call resultset and failed
to provide any other resultsets.
 

"Val Mazur" wrote:

> Hi,
>
> How do you reference second recordset? Do you use NextRecordset method to
> get it?
>
> --
> Val Mazur
> Microsoft MVP
>
>
> "PKNET" <PKNET@discussions.microsoft.com> wrote in message
> news:8E920A8F-E804-4359-B1A8-4F4B0A756ABA@microsoft.com...
> > There is a missing resultset anomaly when calling a stored procedure with
> > a
> > nested stored procedure.
> >
> > =========================
> > CREATE PROCEDURE usp_AddTen
> > @nResult int
> > AS
> > SET NOCOUNT ON
> > SET @nResult = @nResult + 10
> > SELECT @nResult AS Total
> > GO
> > =========================
> >
> > =========================
> > CREATE PROCEDURE usp_ProcessTotal
> > AS
> > SET NOCOUNT ON
> > EXECUTE usp_AddTen 123
> > SET @nDog = 1000
> > SELECT @nDog AS MyTestOutput
> > GO
> > =========================
> >
> > When I call a stored procedure with a nested stored procedure via the ADO
> > Recordset Object, I only get the first resultset back. For instance, if I
> > call usp_ProcessTotal it will only return a single record and in this case
> > I
> > will get a column named "Total' with a 133 value. The recordset output
> > for
> > usp_ProcessTotal is missing, in particular, "MyTestOutput" doesn't exist
> > as a
> > recordset when called from ADO.
> >
> > The strange thing is that is I call the usp_ProcessTotal from the Query
> > Analyzer, it will return two recordsets, the first one is a column named
> > "Total" with 133 in it and the second recordset is "MyTestOutput" with
> > 1000
> > in it. The Query Analyzer output contains two recordsets.
> >
> > This isn't a SET NOCOUNT ON issue or a get/move to next record issue. The
> > recordset for MyTestOutput doesn't seem to exist when calling nested
> > stored
> > procedure (or variations on this example like calling extended stored
> > procedures within a main stored procedure). In other words, there is only
> > a
> > single resultset returned to the client. If I comment out the nested sp
> > call
> > to usp_AddTen, then I will get the missing recordset "MyTestOutput" with
> > 1000
> > in it. For some reason, a nested stored procedure call fails to obey the
> > SET
> > NOCOUNT ON or to allow a multiple resultset be returned from the main
> > calling
> > stored procedure.
> >
> > Normally, I would not use a recordset as output and use OUTPUT variables
> > instead to solve the problem but this is not possible for our application
> > because the ADO Command Object does not exist in our ADO library (i.e.
> > ADOCE,
> > CEFusion, etc.) and we are precluded from ever using OUTPUT variables.
> >
> > Is there a method to tell SQL Server 2000 (SP3) that I have no interest in
> > the nested stored procedure resultset and only want the last SELECT
> > statement
> > in usp_ProcessTotal to return a recordset?
> >
> > How can I suppress nested stored procedure resultset or find a solution
> > that
> > will properly return multiple recordsets to the client?
> >
> > Any solutions for the missing resultset when calling a stored procedure
> > with
> > nested stored procedures?
> >
> > Thanks....
> >
> >
> >
>
>
>

Relevant Pages
Loading