Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old November 28th, 2005, 04:31 PM
Authorized User
 
Join Date: Nov 2004
Location: , , .
Posts: 57
Thanks: 0
Thanked 0 Times in 0 Posts
Default Age Old ODBC Sql Server Driver Timeout

This has got to be one of the most hashed topics I have read, but there still seems to be no answer to the question that works for me.

I am using a query that has to do a lot of processing to summarize many records. The queries take about a minute each and there are 10 of them. No matter what I do, I still get the same old error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e31'

[Microsoft][ODBC SQL Server Driver]Timeout expired

/billing/billing_balance.asp, line 594

Here is the code:

Set conn = Server.CreateObject("ADODB.Connection")
conn.ConnectionTimeout = 0
conn.CommandTimeout = 0
conn = "dsn=corporateops;Uid=" & Username & ";Pwd=" & Password & ";"
Set rs = Server.CreateObject("ADODB.Recordset")
Location_ID = 3
sql = " SELECT convert(varchar(15),common_name) As yard_name, convert(money, " &_
      " Sum(FEED_QTY*ri.AS_FED_PER*CP.STD_PRICE_PER_UNIT/100)) as Dollars, " &_
      " convert(int, Sum(FEED_QTY*ri.AS_FED_PER/100)) as Qty " &_
      " FROM feedback..PEN_FEEDING pf (nolock), feedback..commodity_price cp (nolock), " &_
      " feedback..ration_ingredient ri (nolock), feedback..location l (nolock) " &_
      " where pf.location_id = " & Location_ID & " "&_
      " and pf.location_id = ri.location_id " &_
      " and pf.location_id = l.location_id " &_
      " and pf.location_id = cp.location_id " &_
      " and pf.ration_id = ri.ration_id " &_
      " and ri.commodity_id = cp.commodity_id " &_
      " and pf.feed_date >= '" & From_Date & "' " &_
      " and pf.feed_date <= '" & To_Date & "' " &_
      " and pf.feed_date >= ri.eff_from " &_
      " and (pf.feed_date < ri.eff_to or ri.eff_to is null) " &_
      " and pf.feed_date >= cp.eff_from " &_
      " and (pf.feed_date < cp.eff_to or cp.eff_to is null) " &_
      " group by common_name "
rs62.Open sql, conn

Can anyone please tell me how to extend the query timeout of this thing?



  #2 (permalink)  
Old December 5th, 2005, 11:39 AM
Authorized User
 
Join Date: Nov 2004
Location: , , .
Posts: 57
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Wow, does, no one have and answer?

Busher
  #3 (permalink)  
Old December 5th, 2005, 04:48 PM
Friend of Wrox
 
Join Date: Jul 2003
Location: , , United Kingdom.
Posts: 683
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi Busher,

Have you tried setting the CommandTimeout property?

http://msdn.microsoft.com/library/de...andtimeout.asp

HTH,

Chris

  #4 (permalink)  
Old December 6th, 2005, 04:02 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

use connection.execute to get your recordset, not rs.open, then it will use the unlimited commandtimeoout you're trying to set
  #5 (permalink)  
Old December 7th, 2005, 11:16 AM
Authorized User
 
Join Date: Nov 2004
Location: , , .
Posts: 57
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the replies!!! Can you give me an example of the connection.execute. I am not familiar with it. I will do some research on it in the mean time. Thanks for the replies.

btw - I did try the commandtimeout and the connectiontimeout.

busher

  #6 (permalink)  
Old December 7th, 2005, 12:10 PM
Authorized User
 
Join Date: Nov 2004
Location: , , .
Posts: 57
Thanks: 0
Thanked 0 Times in 0 Posts
Default

GOT IT! Thanks guys/girls. I am now going to post the same question on the .NET forum to see what the fix is there. Do you happen to know if there is the same type of functionality in .NET?

busher



Similar Threads
Thread Thread Starter Forum Replies Last Post
[Microsoft][ODBC SQL Server Driver] TIME OUT ERROR rtr1900 ASP.NET 2.0 Basics 1 November 12th, 2008 09:36 AM
[Microsoft][ODBC SQL Driver]Connection is busy muthumari_p2003@yahoo.com Pro JSP 0 May 8th, 2006 07:51 AM
SQL ODBC Driver Pragnesh1205 SQL Server 2000 6 October 31st, 2005 01:15 PM
java.sql.SQLException: [Microsoft][ODBC Driver Man pvsharinath@gmail.com Java Databases 1 May 5th, 2005 02:40 AM
ODBC driver 2000.81.7713.00 for SQL 7.0 Jai SQL Server 2000 1 October 13th, 2003 11:56 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.