Solved

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

Posted on 2008-10-30
5
1,183 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 142

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Modifying SQL 2008/2012 PARTITIONS 3 55
Another way of doing this SQL 8 46
SQL Trigger selecting another database 4 34
Following an example - removing duplicate strings 4 50
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
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…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

910 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

16 Experts available now in Live!

Get 1:1 Help Now