Errors after upgrading SQL Server 2000 database compatibility level

[paulh@esellerate.net]

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]]

Re: Errors after upgrading SQL Server 2000 database compatibility level

paulh@esellerat e.net wrote:[color=blue]
> 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.[/color]

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@esellerate.net]

Re: Errors after upgrading SQL Server 2000 database compatibility level

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.MoveFi rst" is the line
that is referenced by the error. Thanks!


If IsEmpty(Session ("rsPages_Recor dset")) Then
tNeedPages = True
Else
If Session("rsPage s_Recordset") Is Nothing Then
tNeedPages = True
Else
Set rsPages = Session("rsPage s_Recordset")
rsPages.MoveFir st

End If
End If

[Bob Barrows [MVP]]

Re: Errors after upgrading SQL Server 2000 database compatibility level

paulh@esellerat e.net wrote:[color=blue]
> 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.MoveFi rst" is the line
> that is referenced by the error. Thanks!
>
>
> If IsEmpty(Session ("rsPages_Recor dset")) Then
> tNeedPages = True
> Else
> If Session("rsPage s_Recordset") Is Nothing Then
> tNeedPages = True
> Else
> Set rsPages = Session("rsPage s_Recordset")
> rsPages.MoveFir st
>
> End If
> End If[/color]

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=a dStateClosed then
response.write "The recordset is closed"
elseif rspages.bof then
response.write "We're already at the beginning of the recordset"
else
rsPages.MoveFir st
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.