Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 981
  • Last Modified:

ASP DSN Connection Timeout (SQL Server 2K)

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
tekguy4
Asked:
tekguy4
  • 5
  • 4
  • 2
  • +2
1 Solution
 
tekguy4Author Commented:
sorry for the typos.
0
 
darksinclairCommented:
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
 
peh803Commented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
peh803Commented:
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
 
Anthony PerkinsCommented:
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
 
tekguy4Author Commented:
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
 
tekguy4Author Commented:
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
 
Anthony PerkinsCommented:
You should set the CommandTimeout prior to executing the Recordset's Open method.
0
 
tekguy4Author Commented:
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
 
Anthony PerkinsCommented:
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
 
tekguy4Author Commented:
Thanks ac
It works great. Know I just wish I understood the code better, anyone know a good beginners book?
0
 
Anthony PerkinsCommented:
>>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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now