Error: Operation not allowed when object 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 2000 Forums

SQL Server Development (2000)

Error: Operation not allowed when object closed..

Author

Topic

cbrinson
Starting Member

36 Posts

Posted - 2002-05-30 : 02:51:13

I have been wrestling with this problem for days now. I am now so frustrated that I am sure that I am missing something simple and I just can't see it. I have set nocount on so that is not the problem. Here is the proc. I am sure it is ugly but I can't seem to figure out a cleaner way to write it.

CREATE PROCEDURE sp_sessions_upd
@timeout int,
@GUID uniqueidentifier OUTPUT,
@status tinyint OUTPUT
AS

SET NOCOUNT ON

DECLARE @lastloggedtime AS datetime
DECLARE @currenttime AS datetime

SELECT @currenttime = GETDATE()

SELECT @lastloggedtime = last_logged_time
FROM sessions
WHERE session_id = @GUID

IF @@ROWCOUNT > 0
IF DATEADD(mi,@timeout,@lastloggedtime) > @currenttime
BEGIN
UPDATE sessions
SET last_logged_time = @currenttime
WHERE session_id = @GUID

SELECT m.member_id
,m.nickname
,s.last_login
,m.forum_num_threads
,m.forum_order_by
,m.forum_style
FROM sessions s
LEFT JOIN members m ON s.member_id = m.member_ID
WHERE session_id = @GUID
SELECT @status = 1
END

ELSE
BEGIN
DELETE FROM sessions
WHERE session_id = @GUID
SELECT @GUID = NEWID()
INSERT INTO sessions(session_id)
VALUES (@GUID)
SELECT @status = 0
END

ELSE
BEGIN
SELECT @GUID = NEWID()
INSERT INTO sessions(session_id)
VALUES (@GUID)
SELECT @status = 2
END
GO

Basically I want to return profile data if the session is valid. Otherwise I want to return the new session_id. One thing I am unsure of is how you pass in a value and pass out the value using the same variable. ie. @GUID However, I have tried using seperate values and that did not work either. Sometimes I get a operation not allowed when object closed and sometimes I don't. It does not seem to return the correct status code either. A 0 (expired session) is returned even for a valid session. Could someone please tell me some of the dozen things that are wrong with this proc? It does not seem like it should be this difficult to simply return whether a session is valid and a recordset if it is.

Thanks,
Chris

Edited by - cbrinson on 05/30/2002 02:53:05

olily
Starting Member

37 Posts

Posted - 2002-05-30 : 07:05:48

From the error message, it looks like the problem is coming your program and not sp. Maybe something related to the fields that you might extracted from recordset and pass in to your sp. For example, the recordset is close or not even open.

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-05-30 : 11:11:58

Judging by the error, it's a VB one (ASP perhaps???). Looking at the sql of what you got here everything is fine, if you post your code that is calling the stored proc... I can probably help you there.

Edited by - M.E. on 05/30/2002 11:12:35

cbrinson
Starting Member

36 Posts

Posted - 2002-05-30 : 14:47:27

There are so many problems occuring that I am having a hard time debugging any of them. Unfortunately, the source code for the COM object is at home and I don't have access to it but here is where the error occurs:

Basically I create the command object, set up the parameter list and then execute the cmd. The error occurs where the arrow is:
set objRS = objCmd.Execute
-> If Not objRS.EOF Then
arrSession = objRS.GetRows
End If

The operation not allowed error only occurs when the session is expired or the session_id is not found. When the session is valid it works fine. This kind of makes sense since when the session is valid I have a select statement which returns a recordset along with the two output parameters. However, if the session is expired or the session_id does not exist then I only return the output parameters.

Has anyone done something like this before? I guess I could change it so that a recordset is always returned. So when I don't want to return session data in the case that a session is expired I could just set @GUID = null. Null session_id's are not allowed in the sessions table so it would work but that seems like a lame solution.

SELECT m.member_id
,m.nickname
,s.last_login
,m.forum_num_threads
,m.forum_order_by
,m.forum_style
FROM sessions s
LEFT JOIN members m ON s.member_id = m.member_ID
WHERE session_id = @GUID
SELECT @status = 1

I guess I have just never run into a situation before when I want to selectively return a recordset along with output parameters. I am sure I am missing some easy solution. The 2nd problem I need to address is why a 0 (indicating an expired session) seems to be returned even when the session_id does not even exist. I still think there must be some error with that proc somewhere. :-|

Thanks,
Chris

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-30 : 15:03:45

My experience with this error message always involved a recordset object that may not return rows. Since you have INSERT and DELETE operations in addition to SELECT I have a feeling that is the cause. Another thing to remember:

SELECT @status = 0

That does NOT return a recordset/resultset. It does assign a value to a variable, but it is not the same as a result. This however, is a valid result:

SELECT @status AS Status

If you are using an ADO recordset object, you should ensure that the stored procedure ALWAYS returns a resultset from a SELECT query, and that this SELECT statement is the last statement in the procedure (or as close to the last statement as possible)

You might also want to consider incorporating the @status and @GUID variables as column values instead of variables, that way you will ALWAYS return a resultset:

SELECT @GUID AS GUID, @status AS Status, *
FROM FROM sessions s
LEFT JOIN members m ON s.member_id = m.member_ID
WHERE session_id = @GUID

You can then refer to objRS("Status") in your VB code. If there are no rows to return from a table, then:

SELECT @GUID AS GUID, @status AS Status

...will still work as a resultset.

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-05-31 : 10:15:48

Something to consider... It's worked for me in the past. At the top of every page put an if statement to check if the sessionID you are using is not equal to "".. something like
if session = "" then goto invalid.asp?page=search

The invalid.asp will check the page you just came from and then redirect them to a page so that the sessionID can be redone. You can send them back to a login screen, or back to the original search page that set the session.

Kevin Snow
Posting Yak Master

149 Posts

Posted - 2002-05-31 : 10:31:31

Are you using the SQLOLEDB provider?

It has a new methodology. Under the old ODBC connectivity, SPROCS returned only one recordset. Now, multiple recordsets are returned - at least one for any Statement that might normally return a recordset.

This is why "SET NO COUNT ON" is used. To eliminate the record counts that SQLOLDB returns as an additional recordset (Which for some reason, can terminate or close the recordset object(?)).

I recently ran across an example where an update (with NO COUNT ON mind you) in a referenced stored procedure caused the same effect. In this instance "SET ANSI_WARNINGS ON" fixed the problem for some reason. When set just prior to the referenced SP, it worked. Without it, the recordset came back closed.

If you suspect it is a 'multiple recordset thing' causing the problem, you can verify by temporarily switching back to the old ODBC SQL SERVER connection. If it runs on that connection, you know that
the problem is not with your syntax but with your operating parameters.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources