Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > I am having a problem in my asp in the recorsets running at iis 6.0 and sql 2000. Sometimes it return Either BOF or EOF is True with no reason. and then start working again

Reply
Thread Tools

I am having a problem in my asp in the recorsets running at iis 6.0 and sql 2000. Sometimes it return Either BOF or EOF is True with no reason. and then start working again

 
 
Gabriel Mejía
Guest
Posts: n/a
 
      02-21-2005
Services or applications using ActiveX Data Objects (ADO) 2.0 or greater may
intermittently return empty recordsets on queries that should be returning
valid results. At the time the problem occurs, the same queries successfully
return the expected data when run from non-ADO sources, such as from ISQL in
Microsoft SQL Server. This problem predominantly occurs on multi-processor
computers but has also been known to occur on single-processor computers.

As a side effect, the following error may also occur if the application
tries to use the empty recordset:
ADODB.Field error '800a0bcd'
Either BOF or EOF is True, or the current record has been deleted; the
operation requested by the application requires a current record.



Microsoft give a solution at the article Article ID : 230101



but I did it all (I have mdac 2.8 installed and the problem still ocurrs).

I have to tell you that this problem doesnt occurs all the time. When my asp
aplication crashes all the recorset return the message :

ADODB.Field error '800a0bcd'
Either BOF or EOF is True, or the current record has been deleted; the
operation requested by the application requires a current record.

this occurs for some minutes and then start woking again. I have to tell you
that the database is complitly functional when this occurs, and if I do the
same sql querys using query analizer, the sql server return valid results.
The way I can get it work again manualy is by pressing the button "unload"
at the IIS in the "home directory" tab at the apllication settings frame.
this makes the asp work again.

Note: (the iis is in one server and the sql is in other server)


this is the way I connect to the database:

set Recordset6 = Server.CreateObject("ADODB.Recordset")
Recordset6.ActiveConnection = strConect
sql ="SELECT sinValorDominical FROM dbo.tblMarcastarifas with(NOLOCK)
WHERE idtipomarca=" & marcas & " and
datIniciaVigencia<'"+cstr(month(date))+"/"+cstr(day(date))+"/"+cstr(year(dat
e))+"' And
datFinVigencia>'"+cstr(month(date))+"/"+cstr(day(date))+"/"+cstr(year(date))
+"'"
Recordset6.Source=sql
Recordset6.CursorType = 0
Recordset6.CursorLocation = 2
Recordset6.LockType = 1
Recordset6.Open
Recordset6_numRows = 0




Recordset6.close
set Recordset6=nothing



any suggestions





 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      02-21-2005
Please use a shorter subject line. Something like:

IIS6, SQL2000: Intermittent 'Either BOF or EOF is True' Error

More below:

Gabriel Mejía wrote:
<snip>
> ADODB.Field error '800a0bcd'
> Either BOF or EOF is True, or the current record has been deleted; the
> operation requested by the application requires a current record.
>
> this occurs for some minutes and then start woking again. I have to
> tell you that the database is complitly functional when this occurs,
> and if I do the same sql querys using query analizer, the sql server
> return valid results. The way I can get it work again manualy is by
> pressing the button "unload" at the IIS in the "home directory" tab
> at the apllication settings frame. this makes the asp work again.


This sounds as if you are failing to close and destroy your ADO objects when
finished with them.

>
> Note: (the iis is in one server and the sql is in other server)
>
>
> this is the way I connect to the database:
>
> set Recordset6 = Server.CreateObject("ADODB.Recordset")


With IIS6, the "Server." is not necessary and may impair performance.
However, it's got nothing to do with your problem.

Also: recordset6?? Are you really opening 6 recordsets on this page? This
may not be necessary, and not only could it be hurting performance, it could
also have something to do with your problem. Also, how does anyone
maintaining your code know what each recordset contains? Why not use
meaningful variable names? something like:

rsValDom

for this particular recordset?

> Recordset6.ActiveConnection = strConect


This is your problem, right here. Always use an explicit connection object.
Failure to use an explicit connection object can disable pooling
(http://support.microsoft.com/?kbid=271128) leading to problems such as the
one you are experiencing..


> sql ="SELECT sinValorDominical FROM dbo.tblMarcastarifas
> with(NOLOCK) WHERE idtipomarca=" & marcas & " and
> datIniciaVigencia<'"+cstr(month(date))+"/"+cstr(day(date))+"/"+cstr(year(dat
> e))+"' And


You should not be passing today's date to your query. Let SQL Server
calculate it itself. See below for how I would rewrite your code.


Dim cn, cmd, rsValDom, sql
Set cn = CreateObject("adodb.connection")

'hopefully strConect contains an OLE DB connection string like:
strConect = "Provider=SQLOLEDB;" & _
"Data source=your_server_name;" & _
"Initial Catalog=your_database_name;" & _
"User ID=username_not_sa;" & _
"Password=password_for_your_user"

cn.open strConect
'this connection object can be used for all the ado objects on your page.

sql ="SELECT sinValorDominical FROM dbo.tblMarcastarifas " & _
"With(NOLOCK) " & _
"WHERE idtipomarca= ? and datIniciaVigencia< GETDATE() " & _
"And datFinVigencia > GETDATE() "

Set cmd=CreateObject("adodb.command")
cmd.CommandType=1
cmd.CommandText=sql

Set cmd.ActiveConnection = cn
'The "Set" keyword in the previous statement is important

Set rsValDom = cmd.Execute(,array(marcas))
if not rsValDom.EOF then
'process recordset
else
'handle situation where recordset is empty
end if

'IMPORTANT
On Error Resume Next
rsValDom.close:Set rsValDom=nothing
cn.Close: Set cn = nothing


HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      02-21-2005
Bob Barrows [MVP] wrote:
> Please use a shorter subject line. Something like:
> calculate it itself. See below for how I would rewrite your code.
>

I failed to include the advice to investigate using a stored procedure.
Opening 6 recordsets on a page is not a good way to write code. I am
guessing that most, if not all, of the processing you are doing in your
vbscript code could be more efficiently done in a single stored procedure.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      02-21-2005
Bob Barrows [MVP] wrote:
> calculate it itself. See below for how I would rewrite your code.
>

I failed to include the advice to investigate using a stored procedure.
Opening 6 recordsets on a page is not a good way to write code. I am
guessing that most, if not all, of the processing you are doing in your
vbscript code could be more efficiently done in a single stored procedure.

Bob Barrows
-


 
Reply With Quote
 
Gabriel Mejía
Guest
Posts: n/a
 
      02-21-2005
thanks for your help bob.

is there any way I could see the pooling when I use your method and the
pooling when I use mine. (Can I use sql-manager or "select @@connections" to
do that)


"Bob Barrows [MVP]" <(E-Mail Removed)> escribió en el mensaje
news:(E-Mail Removed)...
> Please use a shorter subject line. Something like:
>
> IIS6, SQL2000: Intermittent 'Either BOF or EOF is True' Error
>
> More below:
>
> Gabriel Mejía wrote:
> <snip>
> > ADODB.Field error '800a0bcd'
> > Either BOF or EOF is True, or the current record has been deleted; the
> > operation requested by the application requires a current record.
> >
> > this occurs for some minutes and then start woking again. I have to
> > tell you that the database is complitly functional when this occurs,
> > and if I do the same sql querys using query analizer, the sql server
> > return valid results. The way I can get it work again manualy is by
> > pressing the button "unload" at the IIS in the "home directory" tab
> > at the apllication settings frame. this makes the asp work again.

>
> This sounds as if you are failing to close and destroy your ADO objects

when
> finished with them.
>
> >
> > Note: (the iis is in one server and the sql is in other server)
> >
> >
> > this is the way I connect to the database:
> >
> > set Recordset6 = Server.CreateObject("ADODB.Recordset")

>
> With IIS6, the "Server." is not necessary and may impair performance.
> However, it's got nothing to do with your problem.
>
> Also: recordset6?? Are you really opening 6 recordsets on this page? This
> may not be necessary, and not only could it be hurting performance, it

could
> also have something to do with your problem. Also, how does anyone
> maintaining your code know what each recordset contains? Why not use
> meaningful variable names? something like:
>
> rsValDom
>
> for this particular recordset?
>
> > Recordset6.ActiveConnection = strConect

>
> This is your problem, right here. Always use an explicit connection

object.
> Failure to use an explicit connection object can disable pooling
> (http://support.microsoft.com/?kbid=271128) leading to problems such as

the
> one you are experiencing..
>
>
> > sql ="SELECT sinValorDominical FROM dbo.tblMarcastarifas
> > with(NOLOCK) WHERE idtipomarca=" & marcas & " and
> >

datIniciaVigencia<'"+cstr(month(date))+"/"+cstr(day(date))+"/"+cstr(year(dat
> > e))+"' And

>
> You should not be passing today's date to your query. Let SQL Server
> calculate it itself. See below for how I would rewrite your code.
>
>
> Dim cn, cmd, rsValDom, sql
> Set cn = CreateObject("adodb.connection")
>
> 'hopefully strConect contains an OLE DB connection string like:
> strConect = "Provider=SQLOLEDB;" & _
> "Data source=your_server_name;" & _
> "Initial Catalog=your_database_name;" & _
> "User ID=username_not_sa;" & _
> "Password=password_for_your_user"
>
> cn.open strConect
> 'this connection object can be used for all the ado objects on your page.
>
> sql ="SELECT sinValorDominical FROM dbo.tblMarcastarifas " & _
> "With(NOLOCK) " & _
> "WHERE idtipomarca= ? and datIniciaVigencia< GETDATE() " & _
> "And datFinVigencia > GETDATE() "
>
> Set cmd=CreateObject("adodb.command")
> cmd.CommandType=1
> cmd.CommandText=sql
>
> Set cmd.ActiveConnection = cn
> 'The "Set" keyword in the previous statement is important
>
> Set rsValDom = cmd.Execute(,array(marcas))
> if not rsValDom.EOF then
> 'process recordset
> else
> 'handle situation where recordset is empty
> end if
>
> 'IMPORTANT
> On Error Resume Next
> rsValDom.close:Set rsValDom=nothing
> cn.Close: Set cn = nothing
>
>
> HTH,
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>



 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      02-21-2005
Gabriel Mejía wrote:
> thanks for your help bob.
>
> is there any way I could see the pooling when I use your method and
> the pooling when I use mine. (Can I use sql-manager or "select
> @@connections" to do that)
>

No. You seem to misunderstand what pooling is. Here is some info:
http://msdn.microsoft.com/library/en...l/pooling2.asp
http://support.microsoft.com/?scid=kb;en-us;Q176056
http://support.microsoft.com/default...b;en-us;191572
http://support.microsoft.com/default...b;en-us;324686

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      02-21-2005
Gabriel Mejía wrote:
> thanks for your help bob.
>
> is there any way I could see the pooling when I use your method and
> the pooling when I use mine. (Can I use sql-manager or "select
> @@connections" to do that)
>

Oh! I misunderstood your question. Yes, you can use SQL Profiler to check on
pooling. Look for the execution of the sp_resetconnection procedure.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


 
Reply With Quote
 
Gabriel Mejía
Guest
Posts: n/a
 
      02-21-2005
again thanks


"Bob Barrows [MVP]" <(E-Mail Removed)> escribió en el mensaje
news:%(E-Mail Removed)...
> Gabriel Mejía wrote:
> > thanks for your help bob.
> >
> > is there any way I could see the pooling when I use your method and
> > the pooling when I use mine. (Can I use sql-manager or "select
> > @@connections" to do that)
> >

> Oh! I misunderstood your question. Yes, you can use SQL Profiler to check

on
> pooling. Look for the execution of the sp_resetconnection procedure.
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>



 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      02-21-2005
Bob Barrows [MVP] wrote:
> Gabriel Mejía wrote:
>> thanks for your help bob.
>>
>> is there any way I could see the pooling when I use your method and
>> the pooling when I use mine. (Can I use sql-manager or "select
>> @@connections" to do that)
>>

> Oh! I misunderstood your question. Yes, you can use SQL Profiler to
> check on pooling. Look for the execution of the sp_resetconnection
> procedure.

Correction: sp_reset_connection
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


 
Reply With Quote
 
Gabriel Mejía
Guest
Posts: n/a
 
      02-22-2005
hi bob


exec sp_reset_connection
does this has parameters?.

there is no help about it in sql books

it says
Server: Msg 208, Level 16, State 9, Procedure sp_reset_connection, Line 1
Invalid object name 'sp_reset_connection'.




"Bob Barrows [MVP]" <(E-Mail Removed)> escribió en el mensaje
news:%(E-Mail Removed)...
> Bob Barrows [MVP] wrote:
> > Gabriel Mejía wrote:
> >> thanks for your help bob.
> >>
> >> is there any way I could see the pooling when I use your method and
> >> the pooling when I use mine. (Can I use sql-manager or "select
> >> @@connections" to do that)
> >>

> > Oh! I misunderstood your question. Yes, you can use SQL Profiler to
> > check on pooling. Look for the execution of the sp_resetconnection
> > procedure.

> Correction: sp_reset_connection
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
[False,True] and [True,True] --> [True, True]????? bdb112 Python 45 04-29-2009 02:35 AM
Are All "Either BOF or EOF is True" Errors coded number 800a0bcd GTN170777 ASP General 2 02-28-2008 06:06 PM
"Either BOF or EOF is true " error ; form data problem .Net Sports ASP General 0 01-23-2008 05:48 PM
BOF or EOF is true? Christo ASP General 7 11-15-2006 07:52 AM
If recordset .BOF and rstime.EOF Then giving me "No records" J. Muenchbourg ASP General 0 07-11-2003 03:27 AM



Advertisments