ADO Closed Recordsets w/ NO COUNT ON

[g***@bcdiv.com]

Perhaps someone here can provide some ideas that will nudge me in the
right direction.

I have a stored procedure which is quite large. It outputs several
output parameters (15) and 13 recordsets. SET NO COUNT ON is used at
the beginning of the procedure to supress the (X records affected)
messages from SQL server. (SQL Server 2000)

I'm using ADO with Classic ASP and the command object with parameters.

My tests are on my local machine as I'm working on getting this
debugged before release (XP Pro)

What I've been struggling with is that the stored procedure returns the
first two recordsets okay. Each contains a single column of data and I
can iterate through the records within them without problem.

After the 2nd recordset however, I'm unable to retrieve the recordsets
via the following syntax:

'first and second recordset retrieved successfully - get the third
Set rs3 = rs.NextRecordset
rs3.GetRows
rs3Count = ubound(rs3, 2)
For i = 0 to rs3count
response.write rs3(0,i)
Next

This fails with a 'Current provider does not support returning multiple
recordsets from a single execution' error at the rs.NextRecordset line,
which appears to be due to the recordset being closed (checking
rs3.state before the offending line yields 0).

Running the stored procedure in Query Analyzer yields records for the
3rd recordset with the input parameters I'm using both in QA testing
and from rendering the ASP page.

I've yet to check that the output parameters are output with ADO (they
are in QA), as I cannot get past the closed recordsets.

I've tried removing all but 3 recordsets from the stored procedure,
checking first w/ QA to see that the data is returned (it is). The 3rd
recordset still fails.

Changing the order of the recordsets returned has similar results (ok
in QA, fails in the browser).

Trying to move past the closed recordset with the following also fails
(w/ error 'Current provider does not support returning multiple
recordsets from a single execution.' again apparently due to the closed
recordset.) It renders one "hi" message then fails.
Do Until rs.State = adStateOpen
response.write "hi<br />"
Set rs=rs.NextRecordset
Loop

I've attempted to find where my issue lies via google on ADO, ADO w/
stored procs, etc. but haven't come up with a reason why the recordsets
are being problematic.

I've got another stored procedure w/ both input and output parameters
as well as two recordsets which works via similar ADO code without a
problem.

I prefer to continue to use the command object and command parameters
if possible.

Any ideas on the issue that is causing a closed recordset to be
retrieved -- even with NO COUNT ON in the sproc?

Some things I'm wondering about --
Are there limitations on the data returned by a sproc? (So far though
this shouldn't be an issue based on QA results, but perhaps it is in
ADO? The data is primarily nvarchar and int fields and nothing very
long with about 3 date fields as well.)
What about limitations on the number of parameters or recordsets
returned?
Another thing I'm seeing is messages being generated in QA even with NO
COUNT ON when running the full sproc (which inserts into a temp table,
then manipulates data, before selecting its contents to return as a
recordset). This is without doing a SELECT (outside the sproc while
running it from QA) but only declaring the parameters, setting their
values and EXEC the sproc. In paring down the sproc I'm not seeing
these, but I'm wondering about the inserts into the tmp table --
perhaps these are the culprits?

Any assistance would be helpful! Thanks!!

Regards,
Bonnie

[Erland Sommarskog]

Post by g***@bcdiv.com
What I've been struggling with is that the stored procedure returns the
first two recordsets okay. Each contains a single column of data and I
can iterate through the records within them without problem.
After the 2nd recordset however, I'm unable to retrieve the recordsets
'first and second recordset retrieved successfully - get the third
Set rs3 = rs.NextRecordset
rs3.GetRows
rs3Count = ubound(rs3, 2)
For i = 0 to rs3count
response.write rs3(0,i)
Next
This fails with a 'Current provider does not support returning multiple
recordsets from a single execution' error at the rs.NextRecordset line,
which appears to be due to the recordset being closed (checking
rs3.state before the offending line yields 0).

The true story is that ADO is a piece of crap, and trying to get multiple
result sets from a stored procedure is an exercise that is a bit overly
advanced for ADO. Particularly if the result sets are interleaved with
messages. So you may be trying something that is not possible.

Then again, the fact that it fails at the third resultset gives some hope.
What is not clear, though, is whether this resultset is special, or

Post by g***@bcdiv.com
Another thing I'm seeing is messages being generated in QA even with NO
COUNT ON when running the full sproc (which inserts into a temp table,
then manipulates data, before selecting its contents to return as a
recordset). This is without doing a SELECT (outside the sproc while
running it from QA) but only declaring the parameters, setting their
values and EXEC the sproc. In paring down the sproc I'm not seeing
these, but I'm wondering about the inserts into the tmp table --
perhaps these are the culprits?

What messages do you see? Just the plain (n rows affected), or some
other messages? One situation were you lose completely, is if the
batch you call produce errors. I don't think I've ever been successful
in retrieving result sets that are produced after error messages.

It could help if you post the batch your run from QA, and the output
you get. Also, if possible, post the stored procedure.

[g***@bcdiv.com]

Thanks for your response Erland.

My thinking was also that it was encouraging that the first two
recordsets were being returned.

The messages I see with the full sproc are the 'X rows affected'
messages even though SET NO COUNT ON is used as the first line of the
sproc. With that result, I removed all but 3 record returning SELECTS
from the stored procedure. This trimmed version as well as other
trimmed versions with different SELECTS from the original full sproc
doesn't generate the messages which leads me to believe they're
resulting from the temp table inserts that are in the full sproc.

I've also tried alternating which SELECTS are in the trimmed version
and re-ordering them to see if it was a data issue that was causing the
3rd recordset to fail. That doesn't seem to be the case though since
all combinations fail at the 3rd recordset. I also wondered whether a
single row returned by the sproc SELECT would cause the recordset to be
closed, but changing to different selects which produce more than one
record or changing the parameter input values so that multiple records
are returned by each recordset in the trimmed sproc also yields the
same failure at rs3.

I'd love to post the full sproc, but its part of a proprietary piece so
nix on that.

The data returned from the trimmed sproc (one version) run in QA is
this...
rs1
New York, USA

rs2
2 Medium Scale (10,000 to 100,000 sq.ft.)
3 Small Scale (Under 10,000 sq.ft.)

rs3
college The University of Queensland 1 1999 BArch
Honours Class 1
college Kenmore State Hight School 1 1992 High School
Certificate

The recordset fields are nothing special, names are simple
alphanumerics like SchoolType, SchoolName, Graduated, GradYr. No
reserved words or non alphanumerics. The sproc name is also nothing
special, although I do prefix with 'sp' (not 'sp_').

Here is my QA input...
Sorry but with the proprietary issue, I've removed a number of
parameters (all ints and nvarchars), but again I have no trouble
getting all the params and recordsets in QA. No error messages are
being generated. With the trimmed sproc I also receive no 'X rows
affected' messages. I've also wondered if the nvarchars are a factor in
the failure with ADO, but they don't seem to be a problem for QA. The
max value for nvarchar is nvarchar(100).

DECLARE @returnCode int,@JSID int,@JRID int,@FName nvarchar(15),@MName
nvarchar(50),@LName nvarchar(15),@Address nvarchar(30),@Address2
nvarchar(50),@City nvarchar(15),@Zip nvarchar(10),@Phone
nvarchar(20),@PhoneType nvarchar(50),@Email nvarchar(50),@StartDate
smalldatetime,@Pay nvarchar(20),@Active int,@Completed int,@CreateDate
smalldatetime,@LastUpdate smalldatetime

SET @JSID = 1504
SET @JRID = 61

EXEC @returnCode = spGetXInfo @JSID,@JRID,@FName OUTPUT,@MName
OUTPUT,@LName OUTPUT,@Address OUTPUT,@Address2 OUTPUT,@City OUTPUT,@Zip
OUTPUT,@Phone OUTPUT, @PhoneType OUTPUT,@Email OUTPUT,@StartDate,
@Completed OUTPUT,@CreateDate OUTPUT,@LastUpdate OUTPUT

So it seems the trimmed sproc functions in QA, but fails at the 3rd
recordset in ADO. Any ideas would be helpful.

Based on your response, it seems I should also be considering
alternatives to ADO as well. Any suggestions on that would be helpful.
I have several sprocs that are similar with multiple output parameters
and recordsets. Finding a solution for this one would be a great help
as I need to get this code out.

Thanks again for your assistance. Any help is appreciated. Thanks!

Regards,
Bonnie

Post by Erland Sommarskog

Post by g***@bcdiv.com
What I've been struggling with is that the stored procedure returns the
first two recordsets okay. Each contains a single column of data and I
can iterate through the records within them without problem.

SNIP<<<

This fails with a 'Current provider does not support returning multiple
recordsets from a single execution' error at the rs.NextRecordset line,
which appears to be due to the recordset being closed (checking
rs3.state before the offending line yields 0).

The true story is that ADO is a piece of crap, and trying to get multiple
result sets from a stored procedure is an exercise that is a bit overly
advanced for ADO. Particularly if the result sets are interleaved with
messages. So you may be trying something that is not possible.
Then again, the fact that it fails at the third resultset gives some hope.
What is not clear, though, is whether this resultset is special, or

Post by g***@bcdiv.com
Another thing I'm seeing is messages being generated in QA even with NO
COUNT ON when running the full sproc (which inserts into a temp table,
then manipulates data, before selecting its contents to return as a
recordset). This is without doing a SELECT (outside the sproc while
running it from QA) but only declaring the parameters, setting their
values and EXEC the sproc. In paring down the sproc I'm not seeing
these, but I'm wondering about the inserts into the tmp table --
perhaps these are the culprits?

What messages do you see? Just the plain (n rows affected), or some
other messages? One situation were you lose completely, is if the
batch you call produce errors. I don't think I've ever been successful
in retrieving result sets that are produced after error messages.
It could help if you post the batch your run from QA, and the output
you get. Also, if possible, post the stored procedure.
--
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[g***@bcdiv.com]

An update:

Well, a bit more searching and I found the piece of code that returned
all the resultsets:

Do While (Not ADORs Is Nothing)
If ADORs.State = adStateClosed Then Exit Do
While Not ADORs.EOF
For i = 0 To ADORs.Fields.Count - 1
rStr = rStr & " : " & ADORs(i)
Next i
Debug.Print Mid(rStr, 3, Len(rStr))
ADORs.MoveNext
rStr = ""
Wend
Debug.Print "----------------------"
Set ADORs = ADORs.NextRecordset
Loop

I replaced the Debug.Prints w/ response.writes and used this in place
of the ASP code I'd had for retrieving the recordsets and tried the
full sproc. All recordsets are returned.

In case it helps someone else the source of the snippet is:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q194792

What I'd replaced is this:
'--- rs1
Set rs1 = rsAll.NextRecordset
rs1data= rs1.GetRows
NumRS1 = ubound(rs1data,2)

strRS1= ""
For i = 0 to NumRS1
strRS1 = strRS1 & rs1data(0,i) & "<br />"
Next
... similarly for rs2, rs3 all nested in an If statement checking the
sproc return value = 0.
I'd tried other iterations of this using:
If rsAll.State = adStateOpen Then
rs1= rsAll.NextRecordset
rs1data= rs1.GetRows
End If
But this failed also at the NextRecordset line.

I've confirmed my suspicions that the 'X rows affected messages' were
coming from inserts into temp tables in the full sproc. With the
adjustment in ASP code, this is not a problem and all recordsets are
returned regardless of these messages.

Now on to getting the parameters as well...

Regards,
Bonnie

[Erland Sommarskog]

Post by g***@bcdiv.com
Well, a bit more searching and I found the piece of code that returned

Glad to hear that you got it working! I don't really see the difference
to your original code, but it may be that you were using GetRows and
that choked. Oh, don't get me started on ADO... (But unfortunately
for ASP there is very many alternatives. All I can recommend is to move
to .Net. ADO .Net is really nice. All it has in common with ADO is
three letters...)

Post by g***@bcdiv.com
I've confirmed my suspicions that the 'X rows affected messages' were
coming from inserts into temp tables in the full sproc.

But that is sort of weird. The only thing I can think of is that there
is a SET NOCOUNT OFF hiding somewhere.

[NWdev]

I'm relieved too, but am still working through the details of the
recordsets. Basically what worked was to dump them into one long string
(which I'm still dealing with to re-create the actual recordsets -
ugh). Why the code that I posted worked and not what I'd had previously
is still rather baffling. Perhaps it was the GetRows? Weird.

The other weird thing, as you note is that the temp table inserts
generated the SQL messages without any SET NO COUNT OFF lines anywhere
within the sproc. (Only the beginning SET NO COUNT ON.) In the end I
ended up splitting the full sproc into 2 -- one for the recordsets and
one for the output parameters, though it would seem I shouldn't have
needed to.

ASP.net is in the future for this particular app. Unfortunately I'm
working with legacy stuff and trying to get it to the point of at least
using sprocs instead of embedded SQL statements...

Thanks again for your assistance...

Regards.
Bonnie