Solved

ASP DSN Connection Timeout (SQL Server 2K)

Posted on 2004-08-18
13
952 Views
Last Modified: 2008-02-01
I am having a problem with an ASP page timing out. I am using this DSN connection to get data from a SQL 2K server:

Dim MM_PCWW_STRING
MM_PCWW_STRING = "dsn=***;uid=***;pwd=***;ConnectionTimeout=300;CommandTimeout=600;"
%>

I get the following error when I use this code:

<% Response.ContentType = "application/vnd.ms-excel" %>

to export recordset the recordset into excel, the error is:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E31)
[Microsoft][ODBC SQL Server Driver]Timeout expired

If I return less then 500 records the export usually works fine, more then 500 it timesout. Also I use the same recordsets on another page to display them online and it works everytime (I have displayed up to 1000 records at a time online wihtout issue), never times out.  

Anyone have any suggestion? I developed the pages using Dremanweaver MX, any thoughts would be appreciated? It seems to have to do with the commandtimeout property, but it times out within 30 seconds ecerytime exporting more then 500 records, did I set the property right in the DSN string? (If I export less then 500 redords the XLS page is about 250K in size)

Thanks much
0
Comment
Question by:tekguy4
  • 5
  • 4
  • 2
  • +2
13 Comments
 

Author Comment

by:tekguy4
ID: 11834782
sorry for the typos.
0
 
LVL 5

Expert Comment

by:darksinclair
ID: 11834810
You could remove the time out from IIS.

I believe it's in the virtual directory, if you look into the settings you will see a time out. I think the default is 30 seconds.  Increase that or Set it to 0 to make it be unlimited.

Cheers,
0
 
LVL 19

Expert Comment

by:peh803
ID: 11834857
a sql server timeout shouldn't be affected by the iss timeout, should it?  The sql server command timeout (I believe) is set at 90 seconds by default in enterprise manager for sql statement execution, and upping that is not a great idea.  The IIS timeout is (I think) just a value that will determine how long to wait for a response from the server....

At any rate, as with most timeout errors, your resolution should not be to "outsmart" the timeout, but rather to figure out why it's happening.  A 500 or 1000 record pull of data shouldn't cause a sql server timeout with properly formed statements and nicely normalized, indexed tables.  This is why timeouts exist, right?  To prevent us programmers from writing slow code that frustrates our users....

Anyways, if you'd like to go down the road of optimizing your query / table structures, go ahead and post your code / table structures here.  Otherwise, I'm not sure what to suggest.  

However, keep in mind that anytime you see a timeout expired error (whether it be IIS, SQL Server, or anything else), chances are that you could be doing something a whole lot better than you actually are..

regards,
peh803
0
 
LVL 31

Expert Comment

by:alorentz
ID: 11834871
0
 
LVL 19

Expert Comment

by:peh803
ID: 11834934
yes, exactly what I was talking about....

But again...my advice would be to fix the inefficient statement rather than patching it with a larger timeout that will frustrate your users...

peh803
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11837517
If you want to mask the problem then set the CommandTimeout to 0 (infinite) the default is 30 seconds.  If you want to solve it, as peh803 suggests post the query and any SQL Profile stats you may have.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:tekguy4
ID: 11840590
I will post the statement later, the part I dont understand is that the same  statement is used on the webview page and export page. The webview page never times out, the only real difference between the two pages is the addition of this line: <% Response.ContentType = "application/vnd.ms-excel" %> in the html body and the removal of all the graphics and links off this page (basically I just copied the webview page, removed the links and graphics, added the line to make it export and thought everything should be fine)

The response time for the webview page is very quick and the data comes up in a matter of seconds, again only the eport page times out, is there a better way to export the recordset in asp?  

I am pretty new to this so sorry if this seems basic to people and thanks again for your help.

0
 

Author Comment

by:tekguy4
ID: 11841060
For a quick fix I would like to set the CommandTimeout higher, can I secify the commandtimeout in the dsn connection? (which looks like this right now)


<%
' FileName="Connection_odbc_conn_dsn.htm"
' Type="ADO"
' DesigntimeType="ADO"
' HTTP="false"
' Catalog=""
' Schema=""
Dim MM_PCWW_STRING
MM_PCWW_STRING = "dsn=***;uid=***;pwd=***"
%>


OR should it go in the code of the page (which is this)?

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/PCWW.asp" -->

<%
' *** Restrict Access To Page: Grant or deny access to this page
MM_authorizedUsers=""
MM_authFailedURL="login.asp"
MM_grantAccess=false
If Session("MM_Username") <> "" Then
  If (true Or CStr(Session("MM_UserAuthorization"))="") Or _
         (InStr(1,MM_authorizedUsers,Session("MM_UserAuthorization"))>=1) Then
    MM_grantAccess = true
  End If
End If
If Not MM_grantAccess Then
  MM_qsChar = "?"
  If (InStr(1,MM_authFailedURL,"?") >= 1) Then MM_qsChar = "&"
  MM_referrer = Request.ServerVariables("URL")
  if (Len(Request.QueryString()) > 0) Then MM_referrer = MM_referrer & "?" & Request.QueryString()
  MM_authFailedURL = MM_authFailedURL & MM_qsChar & "accessdenied=" & Server.URLEncode(MM_referrer)
  Response.Redirect(MM_authFailedURL)
End If
%>
<%
Dim Recordset1__MMColParam
Recordset1__MMColParam = "1"
If (Request.QueryString("JobNo")  <> "") Then
  Recordset1__MMColParam = Request.QueryString("JobNo")

End If
%>
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_PCWW_STRING
Recordset1.Source = "SELECT Distinct ClientID, JobNo, DateRec, PCWWSKU, SerialNo, AssetTag, DeviceType, Manufacturer, Model, ModelNo, ScreenSize, ManufacturerDateCode, Processor, ProcessorQuantity, CDROM, RAM, FloppyDrive, PowerSupply, ReceivedGrade, InventoriedDate, HDDQty, HDDSize, HDDType, QtySanitized, QtyDestroyed, SanitizationAudit  FROM dbo.WebView  WHERE JobNo = '" + Replace(Recordset1__MMColParam, "'", "''") + "'"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>PCWW</title>
</head>

<body>
<% Response.ContentType = "application/vnd.ms-excel" %>
<table border="1" cellpadding="0" cellspacing="0">
  <tr>
    <td>ClientID</td>
    <td>JobNo</td>
    <td>DateRec</td>
    <td>PCWWSKU</td>
    <td>SerialNo</td>
    <td>AssetTag</td>
    <td>DeviceType</td>
    <td>Manufacturer</td>
    <td>Model</td>
    <td>ModelNo</td>
    <td>ScreenSize</td>
    <td>ManufacturerDateCode</td>
    <td>Processor</td>
    <td>ProcessorQuantity</td>
    <td>CDROM</td>
    <td>RAM</td>
    <td>FloppyDrive</td>
    <td>PowerSupply</td>
    <td>ReceivedGrade</td>
    <td>InventoriedDate</td>
    <td>HDDQty</td>
    <td>HDDSize</td>
    <td>HDDType</td>
    <td>QtySanitized</td>
    <td>QtyDestroyed</td>
    <td>SanitizationAudit</td>
  </tr>
  <% While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF)) %>
  <tr>
    <td><%=(Recordset1.Fields.Item("ClientID").Value)%></td>
    <td><%=(Recordset1.Fields.Item("JobNo").Value)%></td>
    <td><%=(Recordset1.Fields.Item("DateRec").Value)%></td>
    <td><%=(Recordset1.Fields.Item("PCWWSKU").Value)%></td>
    <td><%=(Recordset1.Fields.Item("SerialNo").Value)%></td>
    <td><%=(Recordset1.Fields.Item("AssetTag").Value)%></td>
    <td><%=(Recordset1.Fields.Item("DeviceType").Value)%></td>
    <td><%=(Recordset1.Fields.Item("Manufacturer").Value)%></td>
    <td><%=(Recordset1.Fields.Item("Model").Value)%></td>
    <td><%=(Recordset1.Fields.Item("ModelNo").Value)%></td>
    <td><%=(Recordset1.Fields.Item("ScreenSize").Value)%></td>
    <td><%=(Recordset1.Fields.Item("ManufacturerDateCode").Value)%></td>
    <td><%=(Recordset1.Fields.Item("Processor").Value)%></td>
    <td><%=(Recordset1.Fields.Item("ProcessorQuantity").Value)%></td>
    <td><%=(Recordset1.Fields.Item("CDROM").Value)%></td>
    <td><%=(Recordset1.Fields.Item("RAM").Value)%></td>
    <td><%=(Recordset1.Fields.Item("FloppyDrive").Value)%></td>
    <td><%=(Recordset1.Fields.Item("PowerSupply").Value)%></td>
    <td><%=(Recordset1.Fields.Item("ReceivedGrade").Value)%></td>
    <td><%=(Recordset1.Fields.Item("InventoriedDate").Value)%></td>
    <td><%=(Recordset1.Fields.Item("HDDQty").Value)%></td>
    <td><%=(Recordset1.Fields.Item("HDDSize").Value)%></td>
    <td><%=(Recordset1.Fields.Item("HDDType").Value)%></td>
    <td><%=(Recordset1.Fields.Item("QtySanitized").Value)%></td>
    <td><%=(Recordset1.Fields.Item("QtyDestroyed").Value)%></td>
    <td><%=(Recordset1.Fields.Item("SanitizationAudit").Value)%></td>
  </tr>
  <%
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  Recordset1.MoveNext()
Wend
%>
</table>
</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>

I know its a basic question, but even after I added the CommandTimeout where I thought it should go, the page is still timing out in 30 seconds.

Thanks much

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11841715
You should set the CommandTimeout prior to executing the Recordset's Open method.
0
 

Author Comment

by:tekguy4
ID: 11842669
Thanks for the tip, but still not getting it.
I added the commandtimeout before opening like this: (and I tried it on some other code lines too):
 
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_PCWW_STRING
Recordset1.Source = "SELECT Distinct ClientID, JobNo, DateRec, PCWWSKU, SerialNo, AssetTag, DeviceType, Manufacturer, Model, ModelNo, ScreenSize, ManufacturerDateCode, Processor, ProcessorQuantity, CDROM, RAM, FloppyDrive, PowerSupply, ReceivedGrade, InventoriedDate, HDDQty, HDDSize, HDDType, QtySanitized, QtyDestroyed, SanitizationAudit  FROM dbo.WebView  WHERE JobNo = '" + Replace(Recordset1__MMColParam, "'", "''") + "'"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
RecordSet1.CommandTimeout = 0
Recordset1.Open()
Recordset1_numRows = 0
%>

I get this error message on the page when I stick it in this code block:

Microsoft VBScript runtime (0x800A01B6)
Object doesn't support this property or method: 'CommandTimeout'
/export.asp, line 43

Like I said I am pretty new to this and from what I can see in examples on the web, I would think this is correct. But I am obviously missing something simple. Any thoughts anyone?
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 11843150
The CommandTimeout is a property of the Connection and Command objects.

Try it this way (untested):
<%
Dim cn
Dim Recordset1
Dim Recordset1_numRows

Set cn = Server.CreateObject("ADODB.Connection")
cn.Open MM_PCWW_STRING
cn.CommandTimeout = 0
cn.CursorLocation = 1
Set Recordset1 = cn.Execute("SELECT Distinct ClientID, JobNo, DateRec, PCWWSKU, SerialNo, AssetTag, DeviceType, Manufacturer, Model, ModelNo, ScreenSize, ManufacturerDateCode, Processor, ProcessorQuantity, CDROM, RAM, FloppyDrive, PowerSupply, ReceivedGrade, InventoriedDate, HDDQty, HDDSize, HDDType, QtySanitized, QtyDestroyed, SanitizationAudit  FROM dbo.WebView  WHERE JobNo = '" + Replace(Recordset1__MMColParam, "'", "''") + "'")
Recordset1_numRows = 0

'  The rest of your code goes here

Recordset1.Close
Set Recordset1 = Nothing

cn.Close
Set cn = Nothing
%>
0
 

Author Comment

by:tekguy4
ID: 11843794
Thanks ac
It works great. Know I just wish I understood the code better, anyone know a good beginners book?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11848817
>>It works great.<<
Not to put a damper on your parade, but as I mentioned before, all you are doing is masking the problem.  A better solution is to find out why it is taking so long and correct.  One possible solution is to index your JobNo column and you may find that you no longer need to set the CommandTimeout (the default 30 seconds is enough)

In any case, thanks for the points.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now