Operation is not allowed when the object is closed

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Operation is not allowed when the object is closed

Author

Topic

ronindev
Starting Member

3 Posts

Posted - 2011-02-01 : 14:02:45

I have a legacy VB6 application that has worked until now running on a SQL 2000 database using the ADODB connection, recordset, command and parameter objects. My VB6 data access methods use these objects to return recordsets through stored procedures. I am (finally) trying to migrate this database to newer versions of SQL but when testing against a copy of my database on SQL 2005, some (not all) of my stored procedures are failing with the error 'Operation is not allowed when the object is closed.'

From reading online about this error, I suspect that the problem has to do with the way recordsets are returned in SQL 2000 vs. SQL 2005/2008. I have set NOCOUNT ON in my stored proc but the error still occurs. One of the stored procedures that exhibits this error returns a recordset that results from a join between several tables. I execute 3 stored procs within this stored proc to get 3 bits of data that I need in the recordset. Then I do a SELECT with several joins and with these 3 bits included and then insert the data into a temp table. Finally the stored proc executes a SELECT on the temp table before dropping the temp table.

The ADODB connection string for the SQL 2000 database is:

Provider=MSDASQL.1;Extended Properties="DRIVER=SQL Server;SERVER=<ServerName>;UID=<username>;PWD=<password;APP=Visual Basic;WSID=<workstationname>;DATABASE=<DBName>"

The connection string for SQL 2005 is:

Provider=SQLOLEDB.1;Initial Catalog=<DBName>;Extended Properties="Server=<ServerName>;UID=<username.;PWD=<password>";Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=<workstationname>;Use Encryption for Data=False;Tag with column collation when possible=False

Here is the code from the VB6 method that calls this stored proc:

Dim objSPParam As ADODB.Parameter
Dim objSPCommand As New ADODB.Command
Dim oRS As New ADODB.RecordSet

With objSPCommand
.ActiveConnection = cnQuadelDB
.CommandType = adCmdStoredProc
.CommandText = "GET_BRANCH_OPTIONS_REQID"
End With

oRS.CursorLocation = adUseClient

Set objSPParam = objSPCommand.CreateParameter("@REQID", adInteger, adParamInput, , goActivityData.REQID)
objSPCommand.Parameters.Append objSPParam
Set objSPParam = objSPCommand.CreateParameter("@ACTIVITY_ID", adInteger, adParamInput, , iActID)
objSPCommand.Parameters.Append objSPParam
Set objSPParam = objSPCommand.CreateParameter("@WORKSTEP_ID", adInteger, adParamInput, , iStepID)
objSPCommand.Parameters.Append objSPParam
Set objSPParam = objSPCommand.CreateParameter("@TRACK_INDEX", adInteger, adParamInput, , lTrackIdx)
objSPCommand.Parameters.Append objSPParam
Set objSPParam = objSPCommand.CreateParameter("@ACTIVITYTYPE", adInteger, adParamInput, , goAppInfo.ActivityType)
objSPCommand.Parameters.Append objSPParam
Set objSPParam = objSPCommand.CreateParameter("@SITE", adVarChar, adParamInput, 8, sSite)
objSPCommand.Parameters.Append objSPParam
Set objSPParam = objSPCommand.CreateParameter("@DEFAULT_BRANCH", adInteger, adParamInput, , lDefaultBranch)
objSPCommand.Parameters.Append objSPParam
Set objSPParam = objSPCommand.CreateParameter("@LOOP_LIMIT_BRANCH", adInteger, adParamInput, , lLoopLimitBranch)
objSPCommand.Parameters.Append objSPParam

oRS.Open objSPCommand, , adOpenStatic, adLockReadOnly

Here is the full text of the stored procedure:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GET_BRANCH_OPTIONS_REQID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GET_BRANCH_OPTIONS_REQID]
GO

/****** Object: StoredProcedure [dbo].[GET_BRANCH_OPTIONS_REQID] Script Date: 01/26/2011 13:52:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET NOCOUNT ON
GO

CREATE PROCEDURE [dbo].[GET_BRANCH_OPTIONS_REQID]

@REQID INTEGER,
@ACTIVITY_ID INTEGER,
@WORKSTEP_ID INTEGER,
@TRACK_INDEX INTEGER,
@ACTIVITYTYPE INTEGER,
@SITE VARCHAR(8),
@DEFAULT_BRANCH INTEGER,
@LOOP_LIMIT_BRANCH INTEGER

AS

BEGIN

DECLARE @BRANCH_STEP_ID INTEGER,
@OPTION_INDEX INTEGER,
@ACTIVE_MASK INTEGER,
@COMPLETE_MASK INTEGER,
@VISIBLE_MASK INTEGER,
@TERMINAL_MASK INTEGER,
@DEFAULT_BRANCH_MASK INTEGER,
@NORMAL_BRANCH_MASK INTEGER,
@LOOP_LIMIT_BRANCH_MASK INTEGER,
@NEXT_WORKSTEP_ID INTEGER,
@BRANCH_DUE_DATE DATETIME,
@BRANCH_VSUSER CHAR(10),
@NEXT_TRACK_INDEX INTEGER,
@STEPSTATUS INTEGER,
@NOT_LOOP_LIMIT_MASK INTEGER

SET @ACTIVE_MASK = 1
SET @COMPLETE_MASK = 2
SET @VISIBLE_MASK = 8
SET @TERMINAL_MASK = 16

SET @DEFAULT_BRANCH_MASK = 1
SET @NORMAL_BRANCH_MASK = 2
SET @LOOP_LIMIT_BRANCH_MASK = 4

IF EXISTS(SELECT name FROM tempdb..sysobjects WHERE name like '#TempWorkstepTable')
DROP TABLE [#TempWorkstepTable]
CREATE TABLE #TempWorkstepTable
(
WORKSTEP_ID int,
WORKSTEP_NAME varchar(80),
WORKSTEP_INDEX int,
LOOP_LIMIT int,
BRANCH_PROMPT varchar(100),
OPTION_INDEX int,
OPTION_LABEL varchar(60),
VALIDATE_PROMPT varchar(100),
ROLE_NAME varchar(30),
SITE varchar(8),
HELP_PAGE int,
DUE_DATE datetime,
ACTUAL_DATE datetime,
NA char(2),
NOTES text,
VSUSER char(10),
TRACK_INDEX int,
ACTIVITYTYPE int,
STATUS int
)

SET @STEPSTATUS = 0

-- If only the default branches are needed, set the mask
IF @DEFAULT_BRANCH = 1
BEGIN
SET @STEPSTATUS = @STEPSTATUS | @DEFAULT_BRANCH_MASK
END

-- If the loop limit has been reached, check for loop limit branching options first
-- If none are found, then return the standard branching options
-- If the loop limit has not been reached, check for standard branching options only
IF @LOOP_LIMIT_BRANCH = 1
BEGIN
SET @STEPSTATUS = @STEPSTATUS | @LOOP_LIMIT_BRANCH_MASK
END
ELSE
BEGIN
SET @STEPSTATUS = @STEPSTATUS | @NORMAL_BRANCH_MASK
END

DECLARE curBranchOpt CURSOR LOCAL FOR
SELECT BRANCH.OPTION_INDEX, BRANCH.BRANCH_STEP_ID
FROM dbo.ACTIVITY_WORKSTEPS ORIGSTEP
INNER JOIN dbo.CA_WORKSTEP_BRANCH BRANCH
ON ORIGSTEP.WORKSTEP_ID = BRANCH.ORIG_STEP_ID
AND ORIGSTEP.ACTIVITY_ID = BRANCH.ACTIVITY_ID
AND ORIGSTEP.ACTIVITYTYPE & BRANCH.ACTIVITYTYPE = BRANCH.ACTIVITYTYPE
AND ORIGSTEP.SITE = BRANCH.SITE
AND BRANCH.STATUS & @STEPSTATUS = @STEPSTATUS
WHERE ORIGSTEP.WORKSTEP_ID = @WORKSTEP_ID
AND ORIGSTEP.ACTIVITY_ID = @ACTIVITY_ID
AND ORIGSTEP.ACTIVITYTYPE & @ACTIVITYTYPE = @ACTIVITYTYPE
AND ORIGSTEP.SITE = @SITE
ORDER BY BRANCH.OPTION_INDEX

OPEN curBranchOpt

IF @LOOP_LIMIT_BRANCH = 1

-- If the loop limit has been reached, but there are no loop limit branches
-- then look for the normal branching options
BEGIN

IF (SELECT @@CURSOR_ROWS) = 0

BEGIN

CLOSE curBranchOpt
DEALLOCATE curBranchOpt

-- Remove the loop limit mask and query again
SET @NOT_LOOP_LIMIT_MASK = ~(@LOOP_LIMIT_BRANCH_MASK)
SET @STEPSTATUS = @STEPSTATUS & @NOT_LOOP_LIMIT_MASK
SET @STEPSTATUS = @STEPSTATUS | @NORMAL_BRANCH_MASK

DECLARE curBranchOpt CURSOR LOCAL FOR
SELECT BRANCH.OPTION_INDEX, BRANCH.BRANCH_STEP_ID
FROM dbo.ACTIVITY_WORKSTEPS ORIGSTEP
INNER JOIN dbo.CA_WORKSTEP_BRANCH BRANCH
ON ORIGSTEP.WORKSTEP_ID = BRANCH.ORIG_STEP_ID
AND ORIGSTEP.ACTIVITY_ID = BRANCH.ACTIVITY_ID
AND ORIGSTEP.ACTIVITYTYPE & BRANCH.ACTIVITYTYPE = BRANCH.ACTIVITYTYPE
AND ORIGSTEP.SITE = BRANCH.SITE
AND BRANCH.STATUS & @STEPSTATUS = @STEPSTATUS
WHERE ORIGSTEP.WORKSTEP_ID = @WORKSTEP_ID
AND ORIGSTEP.ACTIVITY_ID = @ACTIVITY_ID
AND ORIGSTEP.ACTIVITYTYPE & @ACTIVITYTYPE = @ACTIVITYTYPE
AND ORIGSTEP.SITE = @SITE
ORDER BY BRANCH.OPTION_INDEX

OPEN curBranchOpt

END

END

IF (SELECT @@CURSOR_ROWS) > 0

BEGIN

FETCH NEXT FROM curBranchOpt INTO @OPTION_INDEX, @BRANCH_STEP_ID

WHILE (@@FETCH_STATUS) = 0

BEGIN

SET @NEXT_TRACK_INDEX = (@TRACK_INDEX + 1)

-- CALCULATE THE DUE DATE OF THE BRANCH WORKSTEP
EXEC dbo.CALC_WORKSTEP_DUE_DATE @REQID, @ACTIVITY_ID, @BRANCH_STEP_ID, @ACTIVITYTYPE, @NEXT_TRACK_INDEX, @SITE, @DUE_DATE = @BRANCH_DUE_DATE OUTPUT

-- GET THE VSUSER FOR THE BRANCH WORKSTEP
EXEC dbo.GET_VSUSER_WORKSTEP_ID_REQID @REQID, @ACTIVITY_ID, @BRANCH_STEP_ID, @SITE, @VSUSER = @BRANCH_VSUSER OUTPUT

-- QUERY THE BRANCHING OPTIONS
INSERT INTO #TempWorkstepTable

SELECT BRANCHSTEP.WORKSTEP_ID,
BRANCHSTEP.WORKSTEP_NAME,
BRANCHSTEP.WORKSTEP_INDEX,
BRANCHSTEP.LOOP_LIMIT,
BRANCHSTEP.BRANCH_PROMPT,
BRANCHOPT.OPTION_INDEX,
BRANCHOPT.OPTION_LABEL,
BRANCHSTEP.VALIDATE_PROMPT,
BRANCHSTEP.ROLE_NAME,
BRANCHSTEP.SITE,
BRANCHSTEP.HELP_PAGE,
@BRANCH_DUE_DATE AS DUE_DATE,
TRACK.ACTUAL_DATE,
TRACK.NA,
TRACK.NOTES,
@BRANCH_VSUSER AS VSUSER,
@TRACK_INDEX AS TRACK_INDEX,
@ACTIVITYTYPE AS ACTIVITYTYPE,
(BRANCHSTEP.STATUS | @VISIBLE_MASK) AS STATUS

FROM dbo.CA_WORKSTEP_BRANCH BRANCHOPT

INNER JOIN dbo.ACTIVITY_WORKSTEPS ORIGSTEP
ON BRANCHOPT.ACTIVITY_ID = ORIGSTEP.ACTIVITY_ID
AND BRANCHOPT.ORIG_STEP_ID = ORIGSTEP.WORKSTEP_ID
AND BRANCHOPT.SITE = ORIGSTEP.SITE
AND BRANCHOPT.ACTIVITYTYPE & ORIGSTEP.ACTIVITYTYPE = ORIGSTEP.ACTIVITYTYPE
AND BRANCHOPT.STATUS & @STEPSTATUS = @STEPSTATUS

LEFT OUTER JOIN dbo.ACTIVITY_WORKSTEPS BRANCHSTEP
ON ORIGSTEP.ACTIVITY_ID = BRANCHSTEP.ACTIVITY_ID
AND ORIGSTEP.SITE = BRANCHSTEP.SITE
AND ORIGSTEP.ACTIVITYTYPE = BRANCHSTEP.ACTIVITYTYPE
AND BRANCHSTEP.WORKSTEP_ID = BRANCHOPT.BRANCH_STEP_ID

LEFT OUTER JOIN dbo.CA_WORKSTEP_TRACK TRACK
ON BRANCHSTEP.ACTIVITY_ID = TRACK.ACTIVITY_ID
AND BRANCHSTEP.ACTIVITYTYPE & TRACK.ACTIVITYTYPE = TRACK.ACTIVITYTYPE
AND BRANCHSTEP.WORKSTEP_ID = TRACK.WORKSTEP_ID
AND BRANCHSTEP.SITE = TRACK.SITE
AND TRACK.REQID = @REQID
AND TRACK.TRACK_INDEX = @TRACK_INDEX
AND TRACK.STATUS & @COMPLETE_MASK = 0

WHERE ORIGSTEP.WORKSTEP_ID = @WORKSTEP_ID
AND ORIGSTEP.ACTIVITY_ID = @ACTIVITY_ID
AND ORIGSTEP.SITE = @SITE
AND ORIGSTEP.ACTIVITYTYPE & @ACTIVITYTYPE = @ACTIVITYTYPE
AND BRANCHOPT.OPTION_INDEX = @OPTION_INDEX
ORDER BY BRANCHOPT.OPTION_INDEX

FETCH NEXT FROM curBranchOpt INTO @OPTION_INDEX, @BRANCH_STEP_ID

END

END

SELECT * FROM #TempWorkstepTable
ORDER BY OPTION_INDEX

DROP TABLE #TempWorkstepTable

CLOSE curBranchOpt
DEALLOCATE curBranchOpt

END

This is a long-delayed effort to update our database environment and any light that can be shed on how to get this to work properly and any suggestions for better practices would be greatly appreciated!

ronindev
Starting Member

3 Posts

Posted - 2011-02-01 : 15:56:57

I actually found the solution to this problem.

This is an issue of how recordsets are returned by SQL 2005 vs. SQL 2000. When my VB6 code executes this stored procedure against the SQL 2000 database, the method simply returns the one recordset, no matter how many UPDATE, DELETE or INSERT commands there are in the stored procedure. This recordset simply contains the contents of the final SELECT statement on my temporary table.

However, when the same method executes this stored procedure against my SQL 2005 database, the ADODB recordset object returns multiple recordsets. I had to insert some code that repeatedly calls the NextRecordset method on the ADODB.Recordset to loop through these multiple recordsets until I found a recordset that was not closed. Simple enough!

If oRS.State <> adStateOpen Then
While oRS.State <> adStateOpen
Set oRS = oRS.NextRecordset
Wend
End If

ronindev
Starting Member

3 Posts

Posted - 2011-02-01 : 15:58:57

It is interesting that this stored procedure would still produce multiple recordsets when I had inserted SET NOCOUNT ON into my stored procedure. Any additional comments welcome!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources