Returning RecordSet to ASP is empty

[Eli Sidwell]

Trying to return a Recordset to an ASP and the Recordset is empty.
The StorredProc works in the query analyzer and it even works from a
quick VB app that I wrote to test it.

The storedproc that I am using is fairly complex (creates some
temporary tables and populates them with 'Insert Into Select ...', but
the during testing the only Select statements that return visible rows
is the final one that returns the finished table with an 'Order By
....'.

I am trying to figure out if I am receiving multiple recordsets or if
I am out of environment space. Do the 'Insert Into Select ..'
statements return empty or null recordsets ?

Thanks

Sid ..

 

[Bob Barrows [MVP]]

Trying to return a Recordset to an ASP and the Recordset is empty.
The StorredProc works in the query analyzer and it even works from a
quick VB app that I wrote to test it.

The storedproc that I am using is fairly complex (creates some
temporary tables and populates them with 'Insert Into Select ...', but
the during testing the only Select statements that return visible rows
is the final one that returns the finished table with an 'Order By
...'.

I am trying to figure out if I am receiving multiple recordsets or if
I am out of environment space. Do the 'Insert Into Select ..'
statements return empty or null recordsets ?

Yes. The informational messages (x rows were affected ...) you see in QA are
returned as extra resultsets. You should make a practice of including the
line "SET NOCOUNT ON" at the beginning of all your procedures to suppress
these messages.

HTH,
Bob Barrows

 

[Al Reid]

If you are using SQL Server, put a SET NOCOUNT ON at the beginning of the SP to stop SQL Server from returning empty recordsets for
each non row-returning DML statement. If you cannot do that, user NextRecordset to move to the next one until you get to the one
that is not empty.

 

[Eli Sidwell]

It worked, Thanks for the follow up.

Sid ..

If you are using SQL Server, put a SET NOCOUNT ON at the beginning of the SP to stop SQL Server from returning empty recordsets for
each non row-returning DML statement. If you cannot do that, user NextRecordset to move to the next one until you get to the one
that is not empty.

--
Al Reid

"It ain't what you don't know that gets you into trouble. It's what you know
for sure that just ain't so." --- Mark Twain

 

[Eli Sidwell]

In addition, the Stored Proc returns some values like recordCounts and
other stuff and this is done by a second select statement, but there
appears to be no other recordsets. i.e.

* sp_GetStuff****
Select * from tblSuff Where ...
Select @Count, @OtherSuff

*********

+ ASP +++
RcdSet.Open "Stored_Proc ..."
Do Until RcdSet.EOF
response.write RcdSet(1)
Loop

Set RcdSet = RcdSet.NextRecordSet()
iCount = RcdSet(0)
Response.write iCount

+++++++++

I keep Getting a 'Syntax Error' or 'object required'. How do I get
values returned by the second Select if the are not in a table format
?

Thanks

Sid ..