CURSOR OPERATION CONFLICT

I've got a Windows 2003 SERVER SP1 running sql server 2000 SP4 that is
doing something I've never seen before. Regardless of what database or
table, if I add a new record or try to update a record using an
ADODB.Recordset, I get a CURSOR OPERATION CONFLICT error. This same
code works fine at about 50 other client sites running a similar
environment so I have a feeling there is something wrong with this
particular sql server. The same error occurs on any machine I run the
code on that hits that particular sql server so I don't think it's a
MDAC issue. If I change the cursor location to adUseClient, it works
but that's not a solution for me because it would require alot of code
changes and since this code works fine at other sites, I don't think
that is the solution. Anyone ever encountered a similar issue?
Here's a quick example of how I can reproduce the error, this is just a
vbscript demonstrating the problem:
set cnn=CreateObject("ADODB.Connection")
set rs=CreateObject("ADODB.Recordset")
cnn.open strConnectionString
'Opening it as adOpenKeyset, adLockOptmisitc
rs.Open "Select * From [test]", cnn,1, 3
rs.AddNew
rs.fields("Description").value="test2"
'getting error when the update is called Cursor Operation Conflict
rs.update
rs.close
msgbox "done"
'Windows 2003 Server Standard Edition SERVICE PACK1
'SQL SERVER 2000 SP4
'MDAC VERSION 2.8
I solved the problem.. Another company shares the same sql server that
we use for our application and they had many of the settings under the
"Default Connection Options" (which is under the SQL SERVER
Settings->Connections options tab) checked off, I unchecked all of them
(which is the way it is by default when sql server is installed) and
that cleared up the problem.
|||Just for the record: it seems that it was the "No count" attribute that
was causing the problem. This can be set using:
EXEC sp_configure 'user options', 512
Apparently, all server-side cursors in ADO rely on the number of
affected rows which is returned after the execution of certain
statements. Setting the "No count" attribute in "Default connection
options" (which is equivalent to executing "SET NOCOUNT ON" in each
connection that is opened) suppresses those messages, so ADO gets
confused.
For more informations, see:
http://support.microsoft.com/default...B;EN-US;195491
Razvan