Timeouts

J

Jim in Arizona

I'm doing a query on a webproxy log, which is a pretty large table. I'm only
doing a query on a single days worth, but could be upwards of 250000
records. Originally I got a timeout error saying to increase the time using
Server.ScriptTimeout so I put Server.ScriptTimeout = "900" at the top of my
script. This didn't seem to help. Now I got this error:

a.. Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver]Timeout expired
/suntest.asp, line 39

This time it didn't mention what type of timeout I'm experiencing, at least,
not that I can see. I've seen the ASPFAQ on timeouts
(http://www.aspfaq.com/show.asp?id=2066) but that didn't help me even when I
tried pretty much all of them (on another test a few months back). I'm using
IIS 5. The database I'm using is SQL server 2K. Here's my code.

<%@ Language=VBScript %>

<%
Server.ScriptTimeout = "900"
Dim Conn, sql, daterange, RS

daterange = Request.Form("daterange")

sql = "SELECT * FROM [webproxylog]"
sql = sql & " WHERE (logdate=convert(datetime," & "'" & daterange & "'" &
",102))"

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Driver={SQL
Server};Server=sunspark;Database=ISALogs;Uid=sa;Pwd=slinky;"

Set RS1 = Conn.Execute(sql)

%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Testing</title>
</head>

<body>

<%

If RS1.EOF and RS1.BOF Then
Response.Write("<nobr>There Are No Records</nobr>")
Else
Response.Write("<nobr><h3><u>WebProxyLog Query</u></h3></nobr>")
Response.Write("<table border=""1"" align=""center"" bordercolor=""maroon""
cellspacing=""0"" cellpadding=""7"" width=""95%"">")
Response.Write("<tr><td width=""5%""><h3>Test Column</h3></td></tr>")

RS1.MoveFirst
While Not RS1.EOF
Response.Write("<tr><td>&nbsp;")
Response.Write(RS1.Fields("ClientUserName") & "</td></tr>")
RS1.MoveNext
Wend
Response.Write("</table>")
End If

%>

</body>
</html>



Thanks.
Jim
 
A

Aaron [SQL Server MVP]

- stop using SELECT *
see http://www.aspfaq.com/2096

- use CreateObject instead of Server.CreateObject
see http://www.aspfaq.com/2336

- use the OLEDB provider instead of {SQL Server}
see http://www.aspfaq.com/2126

- make sure there is an index on logdate

- use if RS1.EOF then ... no need to check RS1.BOF

- drop the RS1.MoveFirst call... where else do you expect to be?

- use do while / loop, not while / wend

- create a stored procedure!
see http://www.aspfaq.com/2201

I think the index will be the big one here. I suggest changing daterange to
be in the format YYYYMMDD, so you can drop the convert in the where clause.
If not, and you're sure the result will never be ambiguous (see
http://www.aspfaq.com/2023), then use convert(SMALLDATETIME instead. If the
logdate column only stores dates at midnight with no time component, then
the column should be SMALLDATETIME, not DATETIME. If there is a time
component, then you are going to need to change the where clause slightly
(see http://www.aspfaq.com/2280).

You might want to visit the timeouts article again, if the last time you
tried any of them was "months ago." You might also see this article for
general efficiency suggestions: http://www.aspfaq.com/2424

--
http://www.aspfaq.com/
(Reverse address to reply.)




Jim in Arizona said:
I'm doing a query on a webproxy log, which is a pretty large table. I'm only
doing a query on a single days worth, but could be upwards of 250000
records. Originally I got a timeout error saying to increase the time using
Server.ScriptTimeout so I put Server.ScriptTimeout = "900" at the top of my
script. This didn't seem to help. Now I got this error:

a.. Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver]Timeout expired
/suntest.asp, line 39

This time it didn't mention what type of timeout I'm experiencing, at least,
not that I can see. I've seen the ASPFAQ on timeouts
(http://www.aspfaq.com/show.asp?id=2066) but that didn't help me even when I
tried pretty much all of them (on another test a few months back). I'm using
IIS 5. The database I'm using is SQL server 2K. Here's my code.

<%@ Language=VBScript %>

<%
Server.ScriptTimeout = "900"
Dim Conn, sql, daterange, RS

daterange = Request.Form("daterange")

sql = "SELECT * FROM [webproxylog]"
sql = sql & " WHERE (logdate=convert(datetime," & "'" & daterange & "'" &
",102))"

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Driver={SQL
Server};Server=sunspark;Database=ISALogs;Uid=sa;Pwd=slinky;"

Set RS1 = Conn.Execute(sql)

%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Testing</title>
</head>

<body>

<%

If RS1.EOF and RS1.BOF Then
Response.Write("<nobr>There Are No Records</nobr>")
Else
Response.Write("<nobr><h3><u>WebProxyLog Query</u></h3></nobr>")
Response.Write("<table border=""1"" align=""center"" bordercolor=""maroon""
cellspacing=""0"" cellpadding=""7"" width=""95%"">")
Response.Write("<tr><td width=""5%""><h3>Test Column</h3></td></tr>")

RS1.MoveFirst
While Not RS1.EOF
Response.Write("<tr><td>&nbsp;")
Response.Write(RS1.Fields("ClientUserName") & "</td></tr>")
RS1.MoveNext
Wend
Response.Write("</table>")
End If

%>

</body>
</html>



Thanks.
Jim
Click to expand...
 
D

Dave Anderson

Jim said:
a.. Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver]Timeout expired
Click to expand...

In addition to Aaron's suggestions, take a look at this:

http://msdn.microsoft.com/library/en-us/ado270/htm/mdproconnectiontimeout.asp



--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.
 
J

Jim in Arizona

Whoa Aaron. That's a lot to soak in. I will hit each article and do my best
to comprehend it all. As I progress and hit walls, you all will be hearing
form me!

Thanks for your help.

Jim


Aaron said:
- stop using SELECT *
see http://www.aspfaq.com/2096

- use CreateObject instead of Server.CreateObject
see http://www.aspfaq.com/2336

- use the OLEDB provider instead of {SQL Server}
see http://www.aspfaq.com/2126

- make sure there is an index on logdate

- use if RS1.EOF then ... no need to check RS1.BOF

- drop the RS1.MoveFirst call... where else do you expect to be?

- use do while / loop, not while / wend

- create a stored procedure!
see http://www.aspfaq.com/2201

I think the index will be the big one here. I suggest changing daterange
to
be in the format YYYYMMDD, so you can drop the convert in the where
clause.
If not, and you're sure the result will never be ambiguous (see
http://www.aspfaq.com/2023), then use convert(SMALLDATETIME instead. If
the
logdate column only stores dates at midnight with no time component, then
the column should be SMALLDATETIME, not DATETIME. If there is a time
component, then you are going to need to change the where clause slightly
(see http://www.aspfaq.com/2280).

You might want to visit the timeouts article again, if the last time you
tried any of them was "months ago." You might also see this article for
general efficiency suggestions: http://www.aspfaq.com/2424

--
http://www.aspfaq.com/
(Reverse address to reply.)




Jim in Arizona said:
I'm doing a query on a webproxy log, which is a pretty large table. I'm only
doing a query on a single days worth, but could be upwards of 250000
records. Originally I got a timeout error saying to increase the time using
Server.ScriptTimeout so I put Server.ScriptTimeout = "900" at the top of my
script. This didn't seem to help. Now I got this error:

a.. Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver]Timeout expired
/suntest.asp, line 39

This time it didn't mention what type of timeout I'm experiencing, at least,
not that I can see. I've seen the ASPFAQ on timeouts
(http://www.aspfaq.com/show.asp?id=2066) but that didn't help me even
when I
tried pretty much all of them (on another test a few months back). I'm using
IIS 5. The database I'm using is SQL server 2K. Here's my code.

<%@ Language=VBScript %>

<%
Server.ScriptTimeout = "900"
Dim Conn, sql, daterange, RS

daterange = Request.Form("daterange")

sql = "SELECT * FROM [webproxylog]"
sql = sql & " WHERE (logdate=convert(datetime," & "'" & daterange & "'" &
",102))"

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Driver={SQL
Server};Server=sunspark;Database=ISALogs;Uid=sa;Pwd=slinky;"

Set RS1 = Conn.Execute(sql)

%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Testing</title>
</head>

<body>

<%

If RS1.EOF and RS1.BOF Then
Response.Write("<nobr>There Are No Records</nobr>")
Else
Response.Write("<nobr><h3><u>WebProxyLog Query</u></h3></nobr>")
Response.Write("<table border=""1"" align=""center"" bordercolor=""maroon""
cellspacing=""0"" cellpadding=""7"" width=""95%"">")
Response.Write("<tr><td width=""5%""><h3>Test Column</h3></td></tr>")

RS1.MoveFirst
While Not RS1.EOF
Response.Write("<tr><td>&nbsp;")
Response.Write(RS1.Fields("ClientUserName") & "</td></tr>")
RS1.MoveNext
Wend
Response.Write("</table>")
End If

%>

</body>
</html>



Thanks.
Jim
Click to expand...
Click to expand...
 
I

io

Hi Jim,

In your case it is a ADO query timeout that expires. The default value for
it is 30 secs. If you only want to increase that timeout you can use the
following:

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Driver={SQL
Server};Server=sunspark;Database=ISALogs;Uid=sa;Pwd=slinky;"
Conn.CommandTimeout = <your value in seconds>

If you don't want a query to timeout at all set it to zero.

Cheers
 
J

Jim in Arizona

Perfect! Thanks!

Jim


io said:
Hi Jim,

In your case it is a ADO query timeout that expires. The default value for
it is 30 secs. If you only want to increase that timeout you can use the
following:

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Driver={SQL
Server};Server=sunspark;Database=ISALogs;Uid=sa;Pwd=slinky;"
Conn.CommandTimeout = <your value in seconds>

If you don't want a query to timeout at all set it to zero.

Cheers



Jim in Arizona said:
I'm doing a query on a webproxy log, which is a pretty large table. I'm only
doing a query on a single days worth, but could be upwards of 250000
records. Originally I got a timeout error saying to increase the time using
Server.ScriptTimeout so I put Server.ScriptTimeout = "900" at the top of my
script. This didn't seem to help. Now I got this error:

a.. Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver]Timeout expired
/suntest.asp, line 39

This time it didn't mention what type of timeout I'm experiencing, at least,
not that I can see. I've seen the ASPFAQ on timeouts
(http://www.aspfaq.com/show.asp?id=2066) but that didn't help me even
when I
tried pretty much all of them (on another test a few months back). I'm using
IIS 5. The database I'm using is SQL server 2K. Here's my code.

<%@ Language=VBScript %>

<%
Server.ScriptTimeout = "900"
Dim Conn, sql, daterange, RS

daterange = Request.Form("daterange")

sql = "SELECT * FROM [webproxylog]"
sql = sql & " WHERE (logdate=convert(datetime," & "'" & daterange & "'" &
",102))"

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Driver={SQL
Server};Server=sunspark;Database=ISALogs;Uid=sa;Pwd=slinky;"

Set RS1 = Conn.Execute(sql)

%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Testing</title>
</head>

<body>

<%

If RS1.EOF and RS1.BOF Then
Response.Write("<nobr>There Are No Records</nobr>")
Else
Response.Write("<nobr><h3><u>WebProxyLog Query</u></h3></nobr>")
Response.Write("<table border=""1"" align=""center"" bordercolor=""maroon""
cellspacing=""0"" cellpadding=""7"" width=""95%"">")
Response.Write("<tr><td width=""5%""><h3>Test Column</h3></td></tr>")

RS1.MoveFirst
While Not RS1.EOF
Response.Write("<tr><td>&nbsp;")
Response.Write(RS1.Fields("ClientUserName") & "</td></tr>")
RS1.MoveNext
Wend
Response.Write("</table>")
End If

%>

</body>
</html>



Thanks.
Jim
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Retrieving values of dynamic lists 1
Sort by number of characters 1
Build a table with ASP.net 2
Timeout expired error while using select 1
Registration form 13
Problem in Returning Recordset in ASP 1
Please wait ...message 4
I need help fixing my website 2

Members online

Total: 65 (members: 3, guests: 62)
Robots: 319

Forum statistics

Threads
473,968
Messages
2,570,152
Members
46,698
Latest member
LydiaHalle

Latest Threads

Top