Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

ASP DSN Connection Timeout (SQL Server 2K)

Posted on 2004-08-18
13
Medium Priority
?
977 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: 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!

 
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
 

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 2000 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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

610 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