Advertisement

07.17.2008 at 08:04AM PDT, ID: 23573446
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!
9.2

Report Query Error of [Microsoft][ODBC SQL Server Driver]Timeout expired in ASP with MSSQL

Asked by souldj in Active Server Pages (ASP), MS SQL Server

Tags: ,

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.

Start Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:
194:
195:
196:
197:
198:
199:
200:
201:
202:
203:
204:
205:
206:
207:
208:
209:
210:
211:
212:
213:
214:
215:
216:
217:
218:
219:
220:
221:
222:
223:
224:
225:
226:
227:
228:
229:
230:
231:
232:
233:
234:
235:
236:
237:
238:
239:
240:
241:
242:
243:
244:
245:
246:
247:
248:
249:
250:
251:
252:
253:
254:
255:
256:
257:
258:
259:
260:
261:
262:
263:
264:
265:
266:
267:
268:
269:
270:
271:
272:
273:
274:
275:
276:
277:
278:
279:
280:
281:
282:
283:
284:
285:
286:
287:
288:
289:
290:
291:
292:
293:
294:
295:
296:
297:
298:
299:
300:
301:
302:
303:
304:
305:
306:
307:
308:
309:
310:
311:
312:
313:
314:
315:
316:
317:
318:
319:
320:
321:
322:
323:
324:
325:
326:
327:
328:
329:
330:
331:
332:
333:
334:
335:
336:
337:
338:
339:
340:
341:
342:
343:
344:
345:
346:
347:
348:
349:
350:
351:
352:
353:
354:
355:
356:
357:
358:
359:
360:
361:
362:
363:
364:
365:
366:
367:
368:
369:
370:
371:
372:
373:
374:
375:
376:
377:
378:
379:
380:
381:
382:
383:
384:
385:
386:
387:
388:
389:
390:
391:
392:
393:
394:
395:
396:
397:
398:
399:
400:
401:
402:
403:
404:
405:
406:
407:
408:
409:
410:
411:
412:
413:
414:
415:
416:
417:
418:
419:
420:
421:
422:
423:
424:
425:
426:
427:
428:
429:
430:
431:
432:
433:
434:
435:
436:
437:
438:
439:
440:
441:
442:
443:
444:
445:
446:
447:
448:
449:
450:
451:
452:
453:
454:
455:
456:
457:
458:
459:
460:

<!--    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>&nbsp;</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 %>">[&lt;&lt; 
                                    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 
                                    &gt;&gt;]</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%>
[+][-]07.17.2008 at 08:32AM PDT, ID: 22026586

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: Active Server Pages (ASP), MS SQL Server
Tags: ASP, VBSCRIPT, SQL, [Microsoft][ODBC SQL Server Driver]Timeout expired
Sign Up Now!
Solution Provided By: brad2575
Participating Experts: 1
Solution Grade: A
 
 
[+][-]07.17.2008 at 09:57AM PDT, ID: 22027437

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628