I am having a problem in my asp in the recorsets running at iis 6.0 and sql 2000. Sometimes it retu

[Gabriel Mejía]

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

 

[Bob Barrows [MVP]]

Please use a shorter subject line. Something like:

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

More below:

Gabriel Mejía wrote:

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

 

[Bob Barrows [MVP]]

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

 

[Bob Barrows [MVP]]

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
-

 

[Gabriel Mejía]

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

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-us/dnmdac/html/pooling2.asp
http://support.microsoft.com/?scid=kb;en-us;Q176056
http://support.microsoft.com/default.aspx?scid=kb;en-us;191572
http://support.microsoft.com/default.aspx?scid=kb;en-us;324686

Bob Barrows

 

[Bob Barrows [MVP]]

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.

 

[Gabriel Mejía]

again thanks

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"

 

[Bob Barrows [MVP]]

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

 

[Gabriel Mejía]

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

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]

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

 

[Gabriel Mejía]

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]

this was the error

Error Type:
ADODB.Field (0x80020009)
Either BOF or EOF is True, or the current record has been deleted. Requested
operation requires a current record.
/local/datosiniciales.asp, line 50

 

[Bob Barrows [MVP]]

bob.

conectacadena="Driver={SQL
Server};server="+session("SGP")+";database="+session("Basedatos")+";uid="+se
ssion("clavesgp")+";pwd="+session("passsgp")

No, use SQLOLEDB.

conectacadena="Provider=SQLOLEDB;" & _
"Data Source=" & session("SGP") & ";" & _
"Initial Catalog=" & session("Basedatos") & ";" & _
"User ID=" & session("clavesgp") & ";" & _
"Password=" & 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 + "'"

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

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

Set cmd=CreateObject("adodb.command")
cmd.CommandType=1
cmd.CommandText=sql14
Set cmd.ActiveConnection = cn


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

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

Recordset14=cmd.Execute()

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

'----------------------------------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")

Else
Response.Write "The recordset was empty"
End if

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

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

 

[Gabriel Mejía]

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

 

[Gabriel Mejía]

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.

 

[Bob Barrows [MVP]]

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.

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.

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