Errors after upgrading SQL Server 2000 database compatibility level

[paulh]

Hello, we are preparing for an upgrade to SQL 2005 and as a result of
this I became aware that the compatibility level of one of our
databases was set to level 65 (current SQL server is SQL 2000 SP4).
Managing this server is something I have fallen into, so I am not sure
of the exact reasons its compatibility level has stayed lower. When I
upgrade the level however, some of our ASP pages throw the following
error:

ADODB.Recordset error '800a0bcd'
Either BOF or EOF is True, or the current record has been deleted.
Requested operation requires a current record.

The recordsets for these asp pages are being saved in sessions, which I
know can be problematic. Does anyone have any idea though why this
error would be returned after upgrading the compatibility level?
Thanks a ton for any help.

[Bob Barrows [MVP]]

pa***@esellerate.net wrote:

Hello, we are preparing for an upgrade to SQL 2005 and as a result of
this I became aware that the compatibility level of one of our
databases was set to level 65 (current SQL server is SQL 2000 SP4).
Managing this server is something I have fallen into, so I am not sure
of the exact reasons its compatibility level has stayed lower. When I
upgrade the level however, some of our ASP pages throw the following
error:

ADODB.Recordset error '800a0bcd'
Either BOF or EOF is True, or the current record has been deleted.
Requested operation requires a current record.

The recordsets for these asp pages are being saved in sessions, which
I know can be problematic. Does anyone have any idea though why this
error would be returned after upgrading the compatibility level?
Thanks a ton for any help.

Could you provide a small repro script?
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[paulh]

Thanks for the reply. Here is the snippet of code causing the error.
If this is not what you need to see or if you need to see more of
what's around it, let me know. The "rsPages.MoveFirst" is the line
that is referenced by the error. Thanks!
If IsEmpty(Session("rsPages_Recordset")) Then
tNeedPages = True
Else
If Session("rsPages_Recordset") Is Nothing Then
tNeedPages = True
Else
Set rsPages = Session("rsPages_Recordset")
rsPages.MoveFirst

End If
End If

[Bob Barrows [MVP]]

pa***@esellerate.net wrote:

Thanks for the reply. Here is the snippet of code causing the error.
If this is not what you need to see or if you need to see more of
what's around it, let me know. The "rsPages.MoveFirst" is the line
that is referenced by the error. Thanks!
If IsEmpty(Session("rsPages_Recordset")) Then
tNeedPages = True
Else
If Session("rsPages_Recordset") Is Nothing Then
tNeedPages = True
Else
Set rsPages = Session("rsPages_Recordset")
rsPages.MoveFirst

End If
End If

So I assume this is a disconnected recordset...
You should persist this to a file rather than storing it in session.
My first thought is: never perform a recordset navigation without first
checking for EOF or BOF (depending on the direction in which you are
moving).

You need to determine which condition is actually triggering that error:
If rsPages.State=adStateClosed then
response.write "The recordset is closed"
elseif rspages.bof then
response.write "We're already at the beginning of the recordset"
else
rsPages.MoveFirst
end if

I think it would be helpful to show us the actual contents of the recordset
by using rsPages.Save to save it as XML and posting the result here.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.