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"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
Craigsql
