operation not allowed when object is closed...

I have this stored procedure on SQL 2005:

USE [Eventlog]

GO

/****** Object: StoredProcedure [dbo].[SelectCustomerSoftwareLicenses] Script Date: 08/07/2007 16:56:32 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[SelectCustomerSoftwareLicenses]

(

@.CustomerID char(8)

)

AS

BEGIN

DECLARE @.Temp TABLE (SoftwareID int)

INSERT INTO @.Temp

SELECT SoftwareID FROM Workstations

JOIN WorkstationSoftware ON Workstations.WorkstationID = WorkstationSoftware.WorkstationID

WHERE Workstations.CustomerID = @.CustomerID

UNION ALL

SELECT SoftwareID FROM Notebooks

JOIN NotebookSoftware ON Notebooks.NotebookID = NotebookSoftware.NotebookID

WHERE Notebooks.CustomerID = @.CustomerID

UNION ALL

SELECT SoftwareID FROM Machines

JOIN MachinesSoftware ON Machines.MachineID = MachinesSoftware.MachineID

WHERE Machines.CustomerID = @.CustomerID

DECLARE @.SoftwareInstalls TABLE (rowid int identity(1,1), SoftwareID int, Installs int)

INSERT INTO @.SoftwareInstalls

SELECT SoftwareID, COUNT(*) AS Installs FROM @.Temp

GROUP BY SoftwareID

DECLARE @.rowid int

SET @.rowid = (SELECT COUNT(*) FROM @.SoftwareInstalls)

WHILE @.rowid > 0 BEGIN

UPDATE SoftwareLicenses

SET Installs = (SELECT Installs FROM @.SoftwareInstalls WHERE rowid = @.rowid)

WHERE SoftwareID = (SELECT SoftwareID FROM @.SoftwareInstalls WHERE rowid = @.rowid)

DELETE FROM @.SoftwareInstalls

WHERE rowid = @.rowid

SET @.rowid = (SELECT COUNT(*) FROM @.SoftwareInstalls)

END

SELECT SoftwareLicenses.SoftwareID, Software.Software, SoftwareLicenses.Licenses, SoftwareLicenses.Installs FROM SoftwareLicenses

JOIN Software ON SoftwareLicenses.SoftwareID = Software.SoftwareID

WHERE SoftwareLicenses.CustomerID = @.CustomerID

ORDER BY Software.Software

END

When i execute it in a Query in SQL Studio it works fine, but when i execute it from an ASP page, i get following error:

ADODB.Recordset error '800a0e78'

Operation is not allowed when the object is closed.

/administration/licenses_edit.asp, line 56

Here the conection:

Set OBJdbConnection = Server.CreateObject("ADODB.Connection")
OBJdbConnection.ConnectionTimeout = Session("ConnectionTimeout")
OBJdbConnection.CommandTimeout = Session("CommandTimeout")
OBJdbConnection.Open Session("ConnectionString")
Set SQLStmt = Server.CreateObject("ADODB.Command")
Set RS = Server.CreateObject("ADODB.Recordset")

SQLStmt.CommandText = "EXECUTE SelectCustomerSoftwareLicenses '" & Request("CustomerID") & "'"
SQLStmt.CommandType = 1
Set SQLStmt.ActiveConnection = OBJdbConnection
RS.Open SQLStmt
RS.Close

Can anyone help please?

It this because of the variable tables?

If I recall correctly, an ADODB recordset is not a disconnected object.

You must do your actions between the OPEN and CLOSE.

Are you using VB v6, or Access?

(.NET allows the use of disconnected data using a dataset -NOT a recordset.)

|||

I'm using VB v6 and SQL Server 2005

I am going through my recordset between the open and close.

I think the problem lies in the scope of the variable table in stored procedure, because if i remove that whole chunk with the variable tables, there are no problems.

I have made the script work in totally different way, so i haven't solved the problem, just worked around it

But it would still be nice to know if it is the scope of the varible tables that is being exceeded, and how, if possible to avoid this...?

|||

Just add "set nocount on" as the first statement in your sproc and your problem should go away.

Code Snippet

ALTER PROCEDURE [dbo].[SelectCustomerSoftwareLicenses]

(

@.CustomerID char(8)

)

AS

set nocount on

BEGIN

DECLARE @.Temp TABLE (SoftwareID int)

INSERT INTO @.Temp

SELECT SoftwareID FROM Workstations

JOIN WorkstationSoftware ON Workstations.WorkstationID = WorkstationSoftware.WorkstationID

WHERE Workstations.CustomerID = @.CustomerID

UNION ALL

SELECT SoftwareID FROM Notebooks

JOIN NotebookSoftware ON Notebooks.NotebookID = NotebookSoftware.NotebookID

WHERE Notebooks.CustomerID = @.CustomerID

UNION ALL

SELECT SoftwareID FROM Machines

JOIN MachinesSoftware ON Machines.MachineID = MachinesSoftware.MachineID

WHERE Machines.CustomerID = @.CustomerID

DECLARE @.SoftwareInstalls TABLE (rowid int identity(1,1), SoftwareID int, Installs int)

INSERT INTO @.SoftwareInstalls

SELECT SoftwareID, COUNT(*) AS Installs FROM @.Temp

GROUP BY SoftwareID

DECLARE @.rowid int

SET @.rowid = (SELECT COUNT(*) FROM @.SoftwareInstalls)

WHILE @.rowid > 0 BEGIN

UPDATE SoftwareLicenses

SET Installs = (SELECT Installs FROM @.SoftwareInstalls WHERE rowid = @.rowid)

WHERE SoftwareID = (SELECT SoftwareID FROM @.SoftwareInstalls WHERE rowid = @.rowid)

DELETE FROM @.SoftwareInstalls

WHERE rowid = @.rowid

SET @.rowid = (SELECT COUNT(*) FROM @.SoftwareInstalls)

END

SELECT SoftwareLicenses.SoftwareID, Software.Software, SoftwareLicenses.Licenses, SoftwareLicenses.Installs FROM SoftwareLicenses

JOIN Software ON SoftwareLicenses.SoftwareID = Software.SoftwareID

WHERE SoftwareLicenses.CustomerID = @.CustomerID

ORDER BY Software.Software

END

|||add the following code before "RS.Open SQLStmt"

"Set RS.ActiveConnection = OBJdbConnection"|||

Yes. "SET NOCOUNT ON" will fix your issue. The recordset will not get the resultset from the procedures. Instead of the resultset, the Insert statement's feedback will go.

You can add the "SET NOCOUNT ON" on your sp at first line or you can use the bellow command text,

SQLStmt.CommandText = "SET NOCOUNT ON;EXECUTE SelectCustomerSoftwareLicenses '" & Request("CustomerID") & "'"