By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,738 Members | 1,480 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,738 IT Pros & Developers. It's quick & easy.

ASP sp execution returning closed recordset

P: n/a
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.querystring("SP")
oConn.ConnectionString = "Provider=SQLOLEDB etc"
oConn.Open
set oCmd = Server.CreateObject("ADODB.Command")
oCmd.ActiveConnection = oConn
oCmd.CommandText = StoredProc
oCmd.CommandType = adCmdStoredProc
oCmd.Parameters.Refresh
'code here that populates the parameters of the oCmd object correctly
Set oRs = Server.CreateObject("ADODB.Recordset")
With oRS
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
'execute the SP returning the result into a recordset
.Open oCmd
End With
' Save data into IIS response object
Response.ContentType = "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
Jul 20 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
"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
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.