"Cursor operation conflict" Error

Question

"Cursor operation conflict" Error

austin-567442

Old Hand

Points: 318
More actions
May 20, 2008 at 11:07 pm

#154376

Hello,
I understand that recordsets are not the preferred way to update or insert records in a SQL Server database, but about a year ago I inherited a site that has about 400 of these. They've been working flawlessly up until today. This particular server is using SQL Server 2000 Standard with SP4, on a Windows Server 2003 OS (SP2).
With this example code (in Classic ASP), along with all the other updates and insert....
<%
Dim UpdateSQL
UpdateSQL = "SELECT TOP 1 FirstName FROM Staff WHERE (StaffID = 1);"
Dim UpdateRecordset
Set UpdateRecordset = Server.CreateObject("ADODB.Recordset")
UpdateRecordset.CursorLocation = 1
UpdateRecordset.Open UpdateSQL, "DSN=ProductionDb;", 3, 3
If Not UpdateRecordset.EOF Then
UpdateRecordset.Fields("FirstName") = "NEW FIRST NAME"
UpdateRecordset.Update
End If
UpdateRecordset.Close
Set UpdateRecordset = Nothing
Response.Write("Complete")
%>
are now producing the following error about 75% of the time:
Microsoft OLE DB Provider for ODBC Drivers error '80040e23'
[Microsoft][ODBC SQL Server Driver]Cursor operation conflict
/Graduation/Admin/Events/Update/StaffAssignmentDetails.asp, line 15
This problem was first noticed about 24 hours ago, and has since spread to every other SQL Server database on this machine. Here's what I've done to troubleshoot it:
1. Several sources say to set the "NO COUNT" option off in the "Connections" tab of the "SQL Server Properties" dialog. I tried it both ways, rebooting the server each time to start with a clean slate. This does not work for me.
2. Other sources say to set the "CursorLocation" to the "client." I've tried setting it to both the client and server with no luck.
3. I tried inserting a "MoveFirst" and "MoveLast" statement immediately following the "If Not UpdateRecordset.EOF Then" line. No effect.
4. I've played around with the "cursor type" and "lock type" options without success.
5. There was a hotfox as described in Microsoft Technical Support Article # Q324900. But the Microsoft SQL Server support person I spoke with said this hotfix was included with SQL Server 2000's SP4, which has been installed on this computer. Microsoft is not yet able to offer any additional ideas.
So I'm at a complete loss. Reprogramming all 400+ of these inserts/updates to bypass the recordset altogether isn't really a viable option right now. Has anyone ever seen this type of error before? And were you able to fix it using some other method than what I've already tried above?
Thanks in advance....

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 1

[font="Verdana"]Austin, hope you might have not gone through it. I just found one article related to this issue. Author has mentioned not to use Update method of recordset. For more information refer the below URL.

http://tutorials.aspfaq.com/8000xxxxx-errors/why-do-i-get-80040e23-errors.html

And let us know, if it works for you.

Mahesh

[/font]

MH-09-AM-8694

austin-567442 Old Hand Points: 318 More actions · Answer 2

Hello Mahesh,

Thanks for your feedback. You're correct: ".Update" through a recordset is not a good way to update the database. Unfortunately the problem is that I've got over 400 code modules that were written in a similar fashion as my example above. So I'm trying to figure out if there's a way to resolve this issue without having to go back and recode all 400+.

All of these insert and update operations have worked fine up until the day before yesterday. It seems as though a setting within SQL Server, or something on the machine itself was altered, which is leading to the "Cursor Operation Conflict" errors. But I'm at a complete loss.

MMabbutt SSC Journeyman Points: 97 More actions · Answer 3

LOL, I've got the same problem...

I've just had to build a legacy server (windows 2003 R2 SP2 /SQL server 2000 SP4)

to support Lotus LEI. LEI seems to use a recordset update method so I don't have a choice!

http://support.microsoft.com/kb/324900

The hotfix on the microsoft support site was written for SQL 2000 SP2 - I've tried it on my SP4 system - it doesn't work (what a surprise).

Migrating to SQL2005/8 will cause havoc with the lotus developer.

So has anyone got any ideas??

Mitch.

MMabbutt SSC Journeyman Points: 97 More actions · Answer 4