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?

Wow, does, no one have and answer?

Busher

Hi Busher,

Have you tried setting the CommandTimeout property?

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

HTH,

Chris

use connection.execute to get your recordset, not rs.open, then it will use the unlimited commandtimeoout you're trying to set

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

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