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") & "'"
