ASP sp execution returning closed recordset

[Robin Hammond]

Can anybody tell me why a) when running a stored proc from an asp page to
return a recordset the command succeeds if the sp queries an existing table
directly, but b) if the stored proc populates results into a different
table, temporary table, global temp table, or table variable, then queries
one of these, the asp page reports that the recordset object is closed. If
using a table, I have set grant, select, update, delete permissions for the
asp page user account, so it doesn't appear to be a permissioning issue. If
run in Query Analyser the sp runs fine of course.

Abridged asp code is as follows:
StoredProc = Request.queryst ring("SP")
oConn.Connectio nString = "Provider=SQLOL EDB etc"
oConn.Open
set oCmd = Server.CreateOb ject("ADODB.Com mand")
oCmd.ActiveConn ection = oConn
oCmd.CommandTex t = StoredProc
oCmd.CommandTyp e = adCmdStoredProc
oCmd.Parameters .Refresh
'code here that populates the parameters of the oCmd object correctly
Set oRs = Server.CreateOb ject("ADODB.Rec ordset")
With oRS
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOpti mistic
'execute the SP returning the result into a recordset
.Open oCmd
End With
' Save data into IIS response object
Response.Conten tType = "text/xml"
oRs.Save Response, adPersistXML
'the line above fails with stored procs from example B below, reporting "not
allowed when object is closed", but works with example A

SP Example A - this one works fine
Create Proc spTestA AS
SELECT ID FROM FileList
GO

SP Example B - this one doesn't work from ASP but runs fine in QA
Create Proc spTestB AS
DECLARE @Results Table (ID TinyInt)
INSERT INTO @Results SELECT ID FROM FileList
SELECT ID FROM @Results
GO

I can see the SP executing using profiler when the asp page is called for
both sp's above, so it doesn't appear to be a problem with the execution.
It's something to do with returning the result set from the table variable.

Thanks,

Robin Hammond

[Craig Kelly]

"Robin Hammond" wrote:

<snip>

SP Example B - this one doesn't work from ASP but runs fine in QA
Create Proc spTestB AS
DECLARE @Results Table (ID TinyInt)
INSERT INTO @Results SELECT ID FROM FileList
SELECT ID FROM @Results
GO

<snip>

Robin,

The problem is that you're getting back a closed recordset with "records
affected" info from SQL Server: using the NextRecordset method in ADO will
get the actual recordset you're looking for. A good rule of thumb is to
watch the output from a stored proc in QA: anytime you see a resultset or a
message about records affected, then you know this could pop up.

A more efficient solution (and the one I prefer) if you don't need any data
back but the result of the SELECT is to use SET NOCOUNT...

Create Proc spTestB AS
SET NOCOUNT ON
DECLARE @Results Table (ID TinyInt)
INSERT INTO @Results SELECT ID FROM FileList

SET NOCOUNT OFF
SELECT ID FROM @Results
GO

Craig