Solved

Sql server error 3704: Operation is not allowed when the object is closed

Posted on 2008-10-30
5
1,169 Views
Last Modified: 2012-05-05
I have an asp system that works 24/7.  
At random times, usually once per day or so and usually first thing in the morning, I'll get this error.  The same code has
worked perfectly thousands of times so I assume its not the code.  
Where it happens, I do the following

 Set RS = Server.CreateObject("ADODB.Recordset")
  RS.Open "mytable", Conn, adOpenKeyset, adLockOptimistic, adCmdTable
  RS.AddNew
  RS.Fields("pid") = person_id
  RS.Fields("f_flag") = family_flag
  RS.Fields("time") = Now()
  RS.Update

...and get the error on the update.  Nothing fancy, just simple code.

It seems to hang for up to a minute or so, then errors.

If the person hits 'Back' and retries the post, it works the second time really fast.

Works 20-30 times, then fails the next time.  No events in my event logs show any problems.

SQL Server 2005 is running on Windows 2003 server
Asp is running on a different Windows 2003 server with iis

Thanks

0
Comment
Question by:bigdaddyz99
  • 3
5 Comments
 
LVL 6

Expert Comment

by:bcsql
Comment Utility
Are you updating the same row? A guess is the row is locked when you update it and my return that error when you try to access it while the update is going on. Since it only happens rarely.

0
 
LVL 3

Author Comment

by:bigdaddyz99
Comment Utility
No, the record is not open anywhere else at that time.
Wouldn't I get a different error if it was a lock?
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 400 total points
Comment Utility
please try this alternative:
http://www.asp101.com/samples/viewasp.asp?file=storedprocs.asp

Set cmd = Server.CreateObject("ADODB.Command")
cmd.CommandText =  " INSERT INTO MyTable( PID, f_flag, [time] ) VALUES ( @pid, @f_flag, @time ) "
cmd.Parameters.Append cmd.CreateParameter("@pid", adInteger, adParamInput,, person_id)
cmd.Parameters.Append cmd.CreateParameter("@pid", adInteger, adParamInput,, family_flag)
cmd.Parameters.Append cmd.CreateParameter("@pid", adDBTimestamp, adParamInput,, now() )
cmd.Execute 
 
if you don't want to go that way, please change at least:
  RS.Open "mytable", Conn, adOpenKeyset, adLockOptimistic, adCmdTable 
into:
  RS.Open "select * from mytable where 1 = 0 ", Conn, adOpenKeyset, adLockOptimistic, adCmdText 
in order to avoid that your query/fetch the whole table just to add 1 row.

Open in new window

0
 
LVL 3

Author Comment

by:bigdaddyz99
Comment Utility
we'll give it a whirl.
I'm sure you aren't the master for nothing.
Thank you for your input.
0
 
LVL 3

Author Closing Comment

by:bigdaddyz99
Comment Utility
Evidently, that did the trick.  Thank you very much.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video discusses moving either the default database or any database to a new volume.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now