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

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>
This sounds as if you are failing to close and destroy your ADO objects when
finished with them.

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?

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


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

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)


Show quoteHide quotedatIniciaVigencia<'"+cstr(month(date))+"/"+cstr(day(date))+"/"+cstr(year(dat
Show quoteHide quote

Gabriel Mejía wrote:
Oh! I misunderstood your question. Yes, you can use SQL Profiler to check on
pooling. Look for the execution of the sp_resetconnection procedure.

Bob Barrows [MVP] wrote:
--
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"

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'.




Show quoteHide quote

I did not intend for you to run this procedure (which is not recommended
since it is an internal system procedure). My suggestion was for you to use
SQL Profiler to monitor for its use to detect whether or not pooling is
being used.

Pooling can be controlled only at the client (in this case, the web server).
It cannot be controlled by the server. If pooling is not disabled, this
procedure, which is used to implement pooling at the server, should appear
in a Profiler trace. Or at least, so I've been told. Another way to
determine if pooling is being used is to load a page that connects to the
server, look for the new connection using sp_who2, close the page, run
sp_who2 again to see if the spid goes away or persists for 60 seconds as it
would if pooling is being used. Just be aware that the bad practices I
talked about may disable pooling intermittently, so you may need to monitor
this for a while under various conditions to see if pooling gets disabled.

Pooling is turned on by default in ASP. It can be turned off, either
intentionally as described in the articles I provided, or unintentionally
via the use of bad programming practices. The suggestions I made for your
code will not only help with pooling, they will also help with the overall
efficiency and security of your asp pages. I realize you may be looking at a
large job here, but you should not be looking for excuses to avoid that job.
Some of the problems in your code are leaving your site extremely vulnerable
to being hacked. See these links about sql injection:

http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
http://www.nextgenss.com/papers/advanced_sql_injection.pdf
http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf
http://www.spidynamics.com/papers/SQLInjectionWhitePaper.pdf

HTH,
Bob Barrows

Gabriel Mejía wrote:
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.

I am going to do this changes in some of my asp. If this works in those asp
and it doesnt crash any more in them, I am going to do than in the rest of
them

any way, this I just asked you is only to prove my boss the problem.

thnks again bob


Show quoteHide quote

bob.


I just modified many asp and the way  you told me and  my web site just
crash again some minutes ago.

the next is the asp modified


Note: (I know that the name of the recodrset  is not the most elegant. The
name of the ONLY  recordset is "Recordset14" as you can see)


<%


If session("allow") = "" Then
  response.redirect("ingresoperador.asp")
end if
response.expires=0
Response.Buffer = True
Response.AddHeader "Pragma", "no-cache"
Response.AddHeader "cache-control", "no-store"


'----------------llama el cookie
nombreoficina=Request.Cookies("equipo")("oficina")
nombreequipo=Request.Cookies("equipo")("nombre")
conectacadena="Driver={SQL
Server};server="+session("SGP")+";database="+session("Basedatos")+";uid="+se
ssion("clavesgp")+";pwd="+session("passsgp")


'-------------------------objeto conexion


Set cn = CreateObject("adodb.connection")
cn.open = conectacadena



'---------------------------------------busco el codigo de la oficina que
corresponde al nombre
sql14="SELECT * FROM dbo.tblOficinas WHERE strNombre='" + nombreoficina +
"'"
Set cmd=CreateObject("adodb.command")
cmd.CommandType=1
cmd.CommandText=sql14
Set cmd.ActiveConnection = cn


set Recordset14 = Server.CreateObject("ADODB.Recordset")
Recordset14=cmd.Execute()

'----------------------------------en las siguientes lineas se almcenan las
rutas donde iria a quedar los
'                                  archivos txt con los comprobantes de
impresion y los archivos adjuntos.
session("strRutaComprobantes")=Recordset14("strRutaComprobantes")
session("strRutaAdjuntos")=Recordset14("strRutaAdjuntos")

'Recordset14.close
set Recordset14=nothing
cn.Close
Set cn = nothing
%>
<%

session("dtmfechaaviso")=""
session("numdias")=""
session("tipoclasificado")=""
session("edicion")=""
session("publicacion")=""
session("subdivision")=""
session("idTipoProducto")=""
session("idColor")=""
session("idModulo")=""
session("idTipoEdicion")=""
Session("nombrev")= ""
Session("numeroaviso")=""
Session("nitv")=""
Session("Terminal")=""
Session("Oficina")=""
Session("Seccion")=""
Session("idSubSeccion")=""
Session("idTipoMarca")=""
Session("fondo")=""
Session("borde")=""
Session("titulo")=""
Session("textoTitulo")=""
session("fechas")=""
session("diainicial")=""
nitanterior=Request.QueryString("nitanterior")
telefonoanterior=Request.QueryString("telefonoanterior")
nombreanterior=Request.QueryString("nombreanterior")
direccionanterior=Request.QueryString("direccionanterior")
nombre2anterior=Request.QueryString("nombre2anterior")
%>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<frameset cols="62%,*" frameborder="NO" border="0" framespacing="0">
  <frame name="mainFrame" scrolling="NO"
src="datos%20cliente.asp?nitanterior=<%=nitanterior%>&telefonoanterior=<%=te
lefonoanterior%>&nombreanterior=<%=nombreanterior%>&direccionanterior=<%=dir
eccionanterior%>&nombre2anterior=<%=nombre2anterior%>">
  <frame name="rightFrame" scrolling="NO" noresize src="eleccion1.asp">
</frameset>
<noframes><body bgcolor="#FFFFFF">

</body></noframes>
</html>

Gabriel Mejía wrote:
Server};server="+session("SGP")+";database="+session("Basedatos")+";uid="+se
No, use SQLOLEDB.

conectacadena="Provider=SQLOLEDB;" & _
"Data Source=" & session("SGP") & ";" & _
"Initial Catalog=" & session("Basedatos") & ";" & _
"User ID=" & session("clavesgp") & ";" & _
"Password=" & session("passsgp")

Show quoteHide quoteNo, use parameters. That's the whole point of using a Command object:

sql14="SELECT * FROM dbo.tblOficinas WHERE strNombre=?"


You're missing the "Set" keyword in the following statement. ALWAYS use
"Set" when dealing with object variables.
This statement should be:

Set Recordset14=cmd.Execute(,array(nombreoficina))

Then, don't try to read data from the recordset without checking its EOF
property:

If not Recordset14.EOF then

Else
    Response.Write "The recordset was empty"
End if
If the recordset was empty when you think it should contain data, verify
that nombreoficina contains the data you think it contains, by using
Response.Write nombreoficina

For further debugging, use SQL Profiler to trace the commands sent to your
SQL Server.

HTH,
Bob Barrows

no. it has data

it works all the day with data. this code is just one that crashes. the
problem happen in all the asps. not just the one I put here.

I did the response.write thing a long time ago and is shows data. the
problem is not the query. I am sure of that because I did the response.write
of the query itself and paste it in the query analyser with valid results.

note: I was wrong in something. I am working with IIS 5



Server};server="+session("SGP")+";database="+session("Basedatos")+";uid="+se
Show quoteHide quote

I can replicate the crashes of my web site, and that is  when I run a
desktop visual basic application. this apllication works fine but has a long
transaction that blocks the sql database. After that aplication runs,  the
web page crashes. Note that this is not the only moment when my page
crashes, so the solution is not to quit that aplication. besides that
aplications runs only ones a week.


Show quoteHide quoteServer};server="+session("SGP")+";database="+session("Basedatos")+";uid="+se
Show quoteHide quote

You've lost me. It sounds as if you have more problems than your asp code if
you have blocking occurring on your SQL Server. That long transaction needs
to be addressed.



Gabriel Mejía wrote:
Show quoteHide quoteServer};server="+session("SGP")+";database="+session("Basedatos")+";uid="+se
Show quoteHide quote

Delaying on server side
Calling Exe in Asp
server-side validation