ASP ADO SP call returning closed recordset

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Robin Hammond (rjNOrhSPAM_at_PLEASEnetvigator.com)
Date: 10/20/04 

Date: Wed, 20 Oct 2004 19:54:03 +0800

This was originally posted to comp.databases.ms-sqlserver, but no response
over there, so...

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

Relevant Pages

  • Long-running ASP page using ServerXMLHTTP...
    ... seconds to execute due to slow response from the 3rd party which we have no ... script is running any other requests for ASP pages are 'queued' behind it. ... So no other ASP pages execute until after this script has finished (up to 30 ... are allowed to execute while we are waiting for the response from the 3rd ...
    (microsoft.public.inetserver.asp.db)
  • Passing comma seperated values to a stored proc
    ... They can type in multiple IDs if they are seperated ... I want to pass this string into a Stored Proc and just use an ... "in" command in the where clause to return a recordset back to the ASP page. ... "Syntax error converting the varchar value '123456,123457,123458' to a ...
    (microsoft.public.sqlserver.programming)
  • Re: DTS Through ASP Page permissions
    ... I am running an asp page calling the stored proc. ... when I execute a package from a SQL Agent job using DTSRUN, ... you can capture your DTS execution in a log file. ...
    (microsoft.public.sqlserver.dts)
  • Re: sp_xml_removedocument cant find handle
    ... The handle lives until the connection is reset. ... stored proc and call that instead of doing this inside an ASP page by ... > Microsoft OLE DB Provider for SQL Server error '80040e14' ...
    (microsoft.public.sqlserver.xml)
  • SP and ADO Page Property Not Working Properly
    ... Hi all...I have a stored proc that works fine except I can't get the ... paging property to work in ASP. ... SQL withing the SP is causing the problem. ... In my ASP page, the pagecount property returns ...
    (comp.databases.ms-sqlserver)