asked on
Report Query Error of [Microsoft][ODBC SQL Server Driver]Timeout expired in ASP with MSSQL
I have an ASP Page written to query an SQL 2000 Database and the amount of reocrds is about 400000 lines.
The page is programmed to page the results but the age keeps timeing out.
I have increased the Server.ScriptTimeout = 1250 but still times out.
Could someone help me look at the code and see what else I can do to stop the time outs.
The page is programmed to page the results but the age keeps timeing out.
I have increased the Server.ScriptTimeout = 1250 but still times out.
Could someone help me look at the code and see what else I can do to stop the time outs.
<!-- Start of search code -->
<%Server.ScriptTimeout = 1250%>
<%
'On Error Resume Next
' Our own constants:
Const PAGE_SIZE = 200 ' The size of our pages.
' Declare our variables... always good practice!
Dim strURL ' The URL of this page so the form will work
' no matter what this file is named.
Dim cnnSearch ' ADO connection
Dim rstSearch ' ADO recordset
Dim strDBPath ' path to our Access database (*.mdb) file
Dim strSQL ' The SQL Query we build on the fly
Dim strSearch ' The text being looked for
Dim iPageCurrent ' The page we're currently on
Dim iPageCount ' Number of pages of records
Dim iRecordCount ' Count of the records returned
Dim I ' Standard looping variable
' Retreive the URL of this page from Server Variables
strURL = Request.ServerVariables("SCRIPT_NAME")
' Retreive the term being searched for. I'm doing it on
' the QS since that allows people to bookmark results.
' You could just as easily have used the form collection.
strSearch = Request("what")
' Retrieve page to show or default to the first
If Request.QueryString("page") = "" Then
iPageCurrent = 1
Else
iPageCurrent = CInt(Request.QueryString("page"))
End If
' Since I'm doing this all in one page I need to see if anyone
' has searched for something. If they have we hit the DB.
' O/W I just show the search form and quit.
%>
<script>
var cal=new AOCalendar("cal1");
cal.opt.dtFormat = "$YY/$dm/$dd"// $HH:$mi"; // ex. 04-11-2007
cal.opt.enableTime=true;
cal.opt.themeDir="../themes/default/";
cal.render();
</script>
<TABLE WIDTH="100%" BORDER="0" CELLSPACING="2" CELLPADDING="2">
<TR>
<TD class=inputbox><h4>Archived SMS Reporting </h4></TD>
</TR>
</TABLE>
<FORM name="demoform" onsubmit="" action="<%= strURL %>" method="get">
<input type=hidden name=what value=reports ID="Hidden2">
<table width=100% ID="Table2" cellpadding=2 cellspacing=2>
<tr>
<td width="200" align="left" valign="top"><strong>Start Date:
</strong></td>
<td align="left" valign="top"><strong>
<input type="text" id="startdate" class="memo" value="" name="startdate" />
<img class="PopcalTrigger" align="absmiddle" src="calbtn.gif" width="34" height="22" border="0" alt="" onclick="cal.show('SE', this, 'startdate')" /> </strong></td>
</tr>
<tr align="left" valign="top">
<td width="200"><strong>End Date:
</strong></td>
<td><strong>
<input type="text" id="enddate" class="memo" value="" name="enddate" />
<img class="PopcalTrigger" align="absmiddle" src="calbtn.gif" width="34" height="22" border="0" alt="" onclick="cal.show('SE', this, 'enddate')" /> </strong></td>
</tr>
<tr>
<td width="200"><strong>Containing this number
</strong></td>
<td><strong>
<input name="number" type="text" class="memo" id="number" value="any" size="40">
</strong></td>
</tr>
<tr align="left" valign="top">
<td><strong>With this status</strong> </td>
<td><select name="statusreport" size="1" class="memo">
<option value="all">All reports</option>
<option value="No Report">No reports</option>
<%
Set objDB = Server.CreateObject("ADODB.Connection")
objDB.Open DB_CON
MySQL="Select errormessage, errorcode "&_
"from tbl_errormessages "&_
"ORDER BY errorcode;"
Set ObjRs=objDB.Execute(MySQL)
if objrs.eof then
%>
<option value="">No Status Data</option>
<%
else
Do While not objrs.Eof
%>
<option value="<%=objrs("errorcode")%>"><%=objrs("errormessage")%></option>
<%
Objrs.MoveNext
Loop
%>
</select>
<%
end if
objRS.close()
Set objRS = Nothing
%></td>
</tr>
<tr align="left" valign="top">
<td><strong>Export Results</strong></td>
<td> <input type=radio name=export value="yes" />Yes <br />
<input type=radio name=export value="no" checked />No </td>
</tr>
<tr align="right" valign="top">
<td> </td>
<td align="left"><input type="image" name="imageField" id="imageField" src="images/button_submit.gif" /></td>
</tr>
</table>
</FORM>
<p><br>
<%
startdate = Request("startdate")
enddate = request("enddate")
If startdate <> "" and enddate <> "" Then
'Display all the details passed
' for i=1 to request.querystring.count
' response.write Trim(request.querystring.key(i))&"; "& Trim(request.querystring.item(i)) &"<br>"
' Next
'Response.End
Set cnnSearch = Server.CreateObject("ADODB.Connection")
' This line is for the Access sample database:
cnnSearch.ConnectionTimeout = 120
cnnSearch.CommandTimeout = 120
cnnSearch.Open DB_CON
' Build our query based on the input.
if what = "reports" then
startdate = date()
enddate = date()
else
startdate = Request("startdate")
enddate = request("enddate")
End if
if startdate = "" then
dim weekb4
weekb4 = DateAdd("ww", -1, date()) ' 1 week prior
startdate = Year(weekb4) &"/"& Month(weekb4) &"/"& Day(weekb4)
end if
if enddate = "" then
enddate = Year(date()) &"/"& Month(date()) &"/"& Day(date())
end if
number = request.querystring ("number")
if number = "any" then
number2find = "%"
else
number2find = trim(cstr(number))
end if
messagestatus = request.QueryString ("statusreport")
if messagestatus = "No Report" then
messagestatus = NULL
else
messagestatus =request.QueryString ("statusreport")
end if
orderby = Request.QueryString ("orderby")
if orderby = "" then
orderby = "DateSent Desc"
neworder= "DateSent"
end if
if Request.QueryString ("orderby") = "DateSent" then
orderby = "DateSent"
neworder= "DateSent Desc"
end if
if Request.QueryString ("orderby") = "DateSent Desc" then
orderby = "DateSent Desc"
neworder= "DateSent"
end if
'Response.Write orderby
'Build the SQL Statement
if request.querystring ("what") = "" then
'Search for all messages sent today
select Case number2find
Case Else
strSQL = "SELECT PhoneNumber, Message, DateSent,TimeSent, UserName, Originator, MessageStatus "&_
"FROM messagelog_archive "&_
"WHERE DateSent >= '"& startdate & "' "&_
"AND DateSent <='"& enddate &"' "&_
"AND MessageStatus LIKE '%"& messagestatus &"%' "&_
"AND UserName = '"& Request.Cookies ("web2sms")("username") &"' "&_
"ORDER BY " & orderby &" ;"
End Select
'-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
else
'-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select Case number2find
Case "%"
'Find any number
if ISNULL (messagestatus) then
strSQL = "SELECT PhoneNumber, Message, DateSent,TimeSent, UserName, Originator, MessageStatus "&_
"FROM messagelog_archive "&_
"WHERE DateSent >= '"& startdate & "' "&_
"AND DateSent <='"& enddate &"' "&_
"AND UserName = '"& Request.Cookies ("web2sms")("username") &"' "&_
"AND MessageStatus IS NULL "&_
" ORDER BY " & orderby &" ;"
Else if messagestatus = "all" then
strSQL = "SELECT PhoneNumber, Message, DateSent,TimeSent, UserName, Originator, MessageStatus "&_
"FROM messagelog_archive "&_
"WHERE DateSent >= '"& startdate & "' "&_
"AND DateSent <='"& enddate &"' "&_
"AND UserName = '"& Request.Cookies ("web2sms")("username") &"' "&_
" ORDER BY " & orderby &" ;"
else
strSQL = "SELECT PhoneNumber, Message, DateSent,TimeSent, UserName, Originator, MessageStatus "&_
"FROM messagelog_archive "&_
"WHERE DateSent >= '"& startdate & "' "&_
"AND DateSent <='"& enddate &"' "&_
"AND UserName = '"& Request.Cookies ("web2sms")("username") &"' "&_
"AND MessageStatus LIKE '%"& messagestatus &"%' "&_
" ORDER BY " & orderby &" ;"
End if
end if
Case Else
'Find a specific number
strSQL = "SELECT PhoneNumber, Message, DateSent,TimeSent, UserName, Originator, MessageStatus "&_
"FROM messagelog_archive "&_
"WHERE DateSent >= '"& startdate & "' "&_
"AND DateSent <='"& enddate &"' "&_
"AND Phonenumber LIKE '%"& number2find &"%' "&_
"AND UserName = '"& Request.Cookies ("web2sms")("username") &"' "&_
"ORDER BY " & orderby &" ;"
End Select
end if
'-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
' Execute our query using the connection object. It automatically
' creates and returns a recordset which we store in our variable.
Set rstSearch = Server.CreateObject("ADODB.Recordset")
rstSearch.PageSize = PAGE_SIZE
rstSearch.CacheSize = PAGE_SIZE
' Open our recordset
' Response.Write "<br>"& StrSQL & "<br>"
' Response.End
rstSearch.Open strSQL, cnnSearch, adOpenStatic, adLockReadOnly, adCmdText
' Get a count of the number of records and pages
' for use in building the header and footer text.
iRecordCount = rstSearch.RecordCount
iPageCount = rstSearch.PageCount
%>
<%
if request.querystring ("what") = "" then
'Do Nothing
%>
<%else%>
<center>
A search for messages sent between <%=MyDateFormat(startdate)%> and <%=MyDateFormat(enddate)%> has the following results.
</center>
<%
If iRecordCount = 0 Then
' Display no records error.
%>
<p>
<font color="red" size=3>No records found. Please try again.</font>
</p>
<%
Else
if Request.QueryString ("export")="yes" then
'
else
' Move to the page we need to show.
rstSearch.AbsolutePage = iPageCurrent
' Show a quick status line letting people know where they are:
%>
<center>
<p><b><u>
<%= iRecordCount %> Records Found.
Displaying page <%= iPageCurrent %>
of <%= iPageCount %>:
</u></b></p></center>
<%
' Display a table of the data in the recordset. We loop through the
' recordset displaying the fields from the table and using MoveNext
' to increment to the next record. We stop when we reach EOF.
' For fun I'm combining some fields and showwing you can do more then
' just spit out the data in the form it is in in the table.
%>
<table border="0" align=left width=100% ID="Table1" cellpadding=1 cellspacing=1>
<tr>
<td align=center class="td-heading">Originating Date</td>
<td align=center class="td-heading">Originating Time</td>
<td align=center class="td-heading"> Destination Number</td>
<td align=center class="td-heading">Status</td>
<td align=center class="td-heading">Message</td>
</tr>
<%
Recordcount = 0
Do While Not rstSearch.EOF And recordcount < PAGE_SIZE
'this code alternates the color of the table rows...
iCount = iCount + 1
If iCount Mod 2 = 0 Then
sRowColor = "#8F9AF7"
Else
sRowColor = "#FFFFFF"
End If
%>
<tr align=center valign=top bgcolor="<%=sRowColor%>" >
<td align=center valign=top class=resultsdisplay><%= MyDateFormat(rstSearch.Fields("DateSent").Value) %></td>
<td align=center valign=top class=resultsdisplay><%= Right(rstSearch.Fields("timesent").Value,10) %></td>
<td align=center valign=top class=resultsdisplay><%= rstSearch.Fields("phonenumber").Value %></td>
<td align=center valign=top class=resultsdisplay><!--#include file="report_status.asp"--></td>
<td align=left valign=top class=resultsdisplay><%=rstSearch.Fields("Message").Value%></td>
</tr>
<%
recordcount = recordcount + 1
rstSearch.MoveNext
Loop
%>
<tr>
<td colspan=5 align=center valign=top>
<TABLE WIDTH="100%" BORDER="0" CELLSPACING="2" CELLPADDING="2" ID="Table3" align=center>
<TR>
<TD align=center valign=middle>
<%
' Now we need to show our navigation links:
' Show "previous" and "next" page links which pass the page to
' view our search parameter. You could also use form buttons
' but I find this looks better.
If iPageCurrent > 1 Then
%> <a href="<%= strURL %>?what=<%=Request("what")%>&startdate=<%=startdate%>&enddate=<%=enddate%>&number=<%=number%>&statusreport=<%=messagestatus%>&page=<%= iPageCurrent - 1 %>">[<<
Prev]</a> <%
End If
' You can also show page numbers:
For I = 1 To iPageCount
If I = iPageCurrent Then
%> <%= I %> <%
Else
%> <a href="<%= strURL %>?what=<%=Request("what")%>&startdate=<%=startdate%>&enddate=<%=enddate%>&number=<%=number%>&statusreport=<%=messagestatus%>&page=<%= I %>"><%= I %></a> <%
End If
Next 'I
If iPageCurrent < iPageCount Then
%> <a href="<%= strURL %>?what=<%=Request("what")%>&startdate=<%=startdate%>&enddate=<%=enddate%>&number=<%=number%>&statusreport=<%=messagestatus%>&page=<%= iPageCurrent + 1 %>">[Next
>>]</a> <%
End If
%>
</TD>
</TR>
</TABLE>
</td>
</tr>
</table>
<%
End if
End If
' Close our recordset and connection and dispose of the objects
rstSearch.Close
Set rstSearch = Nothing
cnnSearch.Close
Set cnnSearch = Nothing
End If
%>
<%end if%>
ASPMicrosoft SQL Server
Last Comment
Destiny Amana