Solved

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

Posted on 2008-10-30
5
1,216 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
ID: 22845398
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
ID: 22845567
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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 400 total points
ID: 22846710
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
ID: 22846989
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
ID: 31511855
Evidently, that did the trick.  Thank you very much.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Not selecting duplicate data 6 60
How to simplify my SQL statement? 14 55
LAG_ROWID - how do I get the right order using this query? 2 16
What is this datetime? 1 20
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

828 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