• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1482
  • Last Modified:

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

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
bigdaddyz99
Asked:
bigdaddyz99
  • 3
1 Solution
 
bcsqlCommented:
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
 
bigdaddyz99Author Commented:
No, the record is not open anywhere else at that time.
Wouldn't I get a different error if it was a lock?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
bigdaddyz99Author Commented:
we'll give it a whirl.
I'm sure you aren't the master for nothing.
Thank you for your input.
0
 
bigdaddyz99Author Commented:
Evidently, that did the trick.  Thank you very much.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now