error 800a0cb3: adLockPessimistic vs. adLockReadOnly

[Vanessa]

Everyone,

I have a long story, so please bear with me. Sorry~

Our web server and SQL server are located at the same machine (don’t ask
why…..), and we are working on separating them recently. First our IT people
are going to move everything (both web and SQL server) to a new machine, then
clean up the original machine, and finally move just the web server back to
the original machine and finish the process.

Here is some SPEC of our new machine:
3.0 GHz Xeon Processor
4 GB of ram
IIS 6.0
SQL 2000 with SP3

However, we encounter problems right now at step 1 at the testing stage.
Our ASP scripts are working fine at the current server. But when we perform
testing on the new server, the ASPs are not working with following error:

ADODB.Recordset error '800a0cb3'
Current Recordset does not support bookmarks. This may be a limitation of
the provider or of the selected cursortype.

The scripts are just copied directly from the current server to the new
server (of course, the data source name is updated). Here is the sample
coding:

<!--#include file="../adovbs.inc" -->
<!--#include file="../DatabaseLink.asp" -->
<!--#include file="UserValidate.asp" -->
<%

page=request("page")
if page="" or not isNumeric(page) then page=1

iPageSize = 30
iPageCurrent=cint(page)

connectWord = "N"
strSQL = "SELECT * FROM CSheetDtl"

Set DataRec = Server.CreateObject("ADODB.RecordSet")
DataRec.PageSize = iPageSize
DataRec.CacheSize = iPageSize
DataRec.Open strSQL, DataConn, adOpenKeyset, adLockPessimistic, adCmdText
iPageCount = DataRec.PageCount
if iPageCurrent > iPageCount then iPageCurrent = iPageCount
if iPageCurrent < 1 then iPageCurrent = 1

if not iPageCount = 0 then
DataRec.AbsolutePage = iPageCurrent  ERROR SHOWS ON THIS LINE
%>
<table border=1>
<tr>
<td><b>ID</b></td>
<td><b>CSheet_ID</b></td>
</tr>
<%
iRecordsShown = 0
iColumn=0
bgcolor="E6E6E6"

Do While iRecordsShown < iPageSize And Not DataRec.EOF
%>
<tr>
<td><%=Datarec("ID")%></td>
<td><%=Datarec("CSheet_ID")%></td>
</tr>
<%
DataRec.MoveNext
iRecordsShown = iRecordsShown + 1
iColumn=iColumn+1
if iColumn mod 2 = 0 then
bgcolor="E6E6E6"
else
bgcolor=""
end if
loop

else
response.write "No record found."
end if

DataRec.Close
Set DataRec = Nothing
%>

Where DatabaseLink.asp defines set of variables and DataConn:

<%
Const strConn = "Provider=sqloledb;Initial Catalog=AAAA; User ID=BBBB;
Password=CCCC;data source=database"
Set DataConn=Server.CreateObject("ADODB.Connection")
DataConn.Open strConn
%>

And UserValidate.asp is just checking user login.

After struggling for couple days, I figured out how to resolve the problem.
I changed adLockPessimistic to adLockReadOnly and it works!

Therefore, my question is – is that some setting on the SQL server or web
server side that my IT need to set in order to support this type of calling?
Cause we are using adLockPessimistic at our current server and it is working
just fine.

Thanks so much for reading this!
Vanessa