Error: Operation is not allowed when the object i

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 is not allowed when the object i

Author

Topic

Nitu
Yak Posting Veteran

81 Posts

Posted - 2006-03-14 : 11:49:46

Hi,

I have the following procedure:

CREATE PROCEDURE spApprovedfieldsUpdate
@field1 int,
@iChild int,
@iPricing int
AS
Declare @errorCount int

Begin Transaction

Set nocount on

Set @errorcount = 0

select fields from table where criteria meets for input variables: @field1, @iChild

if certaincondition occurs based on the input variables
call a procedure
else
Begin

DECLARE cursor_tmp CURSOR FOR

Select fields from table2 where table2.field1 = Cast(@field1 as varchar(10))

OPEN cursor_tmp

FETCH cursor_tmp INTO fields from table2

WHILE @@Fetch_Status = 0

BEGIN
on certain condition
call anotherprocedure

-- Get the next row.

FETCH cursor_tmp INTO fields from table2

END

CLOSE cursor_tmp

DEALLOCATE cursor_tmp

End

if @@error <> 0
Begin
RollBack Transaction
Set @errorCount = @@error
End
else
Commit Transaction

select @errorCount as errorCount
GO

the asp code from where i am calling the above procedure is:

ASP code:

sql = "Select srapproval2, srapproval1, field1 From table1"
'Response.write sql
'response.end
rs.open sql, conn
if not rs.eof then
do while not rs.eof
if (rs("srapproval2")=0 or rs("srapproval1") = 0) then
field1 = rs("field1")
set rs1 = CreateObject("ADODB.RecordSet")
sql1 = "spinsertinto_log " & field1
rs1.open sql1, conn
else
if ((rs("srapproval2")) and (rs("srapproval1"))) then
field1 = rs("field1")
set rs1 = CreateObject("ADODB.RecordSet")
sql1 = "spApprovedfieldsUpdate " & field1 & ", " & iChild_tmp & ", " & iPricing
rs1.open sql1, conn
if not rs1.eof then
sqlerror = rs1("errorCount")
if sqlerror <> 0 then
Response.write "Error occured during transaction on field1: " & field1
exit do
end if
end if
end if
end if

rs.movenext
loop
end if

while executing this line, sqlerror = rs1("errorCount"), it giving me the error: Operation is not allowed when the object is closed.

I dont understand whats wrong.

Can sombody please tell me where i am wrong

Thanks a lot in advance
Nitu

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-14 : 11:52:04

U may be trying to "use" recordset, without "opening" it

one possible scenario
rs.open "SQL Query", <Not having the connection>

another
Ur criterias prevent the code to come to the point where rs1 is not having created.

Nitu
Yak Posting Veteran

81 Posts

Posted - 2006-03-14 : 13:01:47

Thanks for you reply srinika,

"U may be trying to "use" recordset, without "opening" it
one possible scenario
rs.open "SQL Query", <Not having the connection>"

I have a include file at the beginning of the code, where the db connection string is opened. and this worked like a charm until i introduced transactions.

so as per your advice, i created a connection everytime, inside the while loop of the asp.

when i called this procedure with a loop of 3 or 4 input values, it worked fine.

but realistically, there will be 100s of value that i have to loop through, and call the procedure so many times. So i tested with like 75 values, it timed out on me.

So is it possible that transactions take a lot more time.

another
Ur criterias prevent the code to come to the point where rs1 is not having created.

the criterias happen properly if iam not using transactions.

Hope you can help me.

actually, i stand corrected, its performing correctly only for one input value only. if i do for more than one its giving me the
error: Operation is not allowed when the object is closed!

Thanks a lot in advance,
Nitu

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-14 : 14:23:28

Check whether the following helps
http://support.microsoft.com/default.aspx/kb/303673

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-14 : 14:31:20

Another thing that I came across was

-- Just remove the

if not rs1.eof then
sqlerror = rs1("errorCount")

Nitu
Yak Posting Veteran

81 Posts

Posted - 2006-03-14 : 16:40:51

Thanks for all you help.

However i dont know where the problem is? Whether, is it in my stored procedure or my asp?

If i dint have transactions in the stored procedure, multiple calls of the procedure is working fine, but when i have transactions, except for a single call, its failing for multiple calls of that procedure.

I came across ado trasactions, that we can directly use transactions in asp. I wanted to try that. When i was changing my stored procedure, by taking out transactions, i got this error when i tried to save my changed storedprocedure:

Error 9002:- The log file for database xyz... is full. Backup the transaction log for the database to free up some log space.

I searched for the reasons for getting such an error. Found out that this can happen if there is a large amount of transactions that have to take place.

In my case, i wouldn't say that it would be that large for few calls of the procedure, but some times, it has to call the procedure like 300 times. Each time there are multiple storedprocedures that this master procedure is calling.

So finally i came to conclusion that transactions is not for my scenario.

If you can think i can still go with transactions with out having these errors, please suggest me with some solutions for my problem.
Because, there is lot of inserts, deletes and updates that happen here. I would feel better if i had transactions in my procedure.

Thanks a lot in advance,
Nitu

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-03-15 : 09:38:19

I think that if you try to loop through more records than are actually in the recordset I belive you will get this error. I've had this problem a few times when debugging som of my stored procedures also and used the PRINT-statement. When I comment out all PRINT-statements and any other debugging stuff that I've created things usually work fine. Try to recreate what you are doing in query analyzer and see if you can track it from there. You can also throw in a few "Response.End" in your ASP for debugging purposes.

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Nitu
Yak Posting Veteran

81 Posts

Posted - 2006-03-15 : 11:21:17

Hey,

You are correct. I had a print statement in the storedprocedure that i forgot to comment it out.

Once i commented it, it works fine now. Thanks a lot.

But i have a problem, when i tried to call this procedure for like say for example 75 times, and another 120 times, the transaction log got filled up(it reached a cap they had of 2gig). Doesnt it delete data from transaction log once it gets commited. If that doesnt happen automatically, is there a way i can do that from the application.

Thanks a lot in advance,
Nitu

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-15 : 12:30:47

Hi Nitu,
U may find the following useful
http://www.nigelrivett.net/TransactionLogFileGrows_1.html

Nitu
Yak Posting Veteran

81 Posts

Posted - 2006-03-15 : 13:08:10

Thanks srinika.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources