?
Solved

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

Posted on 2008-10-30
5
Medium Priority
?
1,339 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1600 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

765 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