Solved

][ODBC SQL Server Driver]Timeout expired

Posted on 2000-04-02
9
719 Views
Last Modified: 2009-04-20
I have a 17GB database in the SQL Server.
I have a stored procedure

After I put all the parameters and tried to run the output page.
I got the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e31'

[Microsoft][ODBC SQL Server Driver]Timeout expired

/reference/list.asp, line 42

line 42 is the "cmdcp.Execute"

I am using SQL Server 7
the version of the SQL service pack is 3.700..
ODBC Connection was ok after verified.
the store procedure was ok when I run it from the SQL Server and it returns the result.

But when I run it from the web. It gave me the timeout expired.

I am thinking about the client side has the timeout problem. I monitor the SQL Server when I run that page, after I got the timeout message, the SQL Server was still executing the query.

Is that a possible way to set a timeout variable? If so, Where ?
Will it be in global.asa??
Please Help!!
0
Comment
Question by:shellyahoo71
  • 5
  • 4
9 Comments
 
LVL 1

Expert Comment

by:ATM
ID: 2678996
You can put 2 timeout values.
1. For executed asp script:
Server.ScriptTimeOut=XX   (sec.)
2. For executed stored procedure
Set commandObj=Server.CreateObjet("ADODB.Command")
commandObj.CommandTimeout=XX (sec.)
Generally script's timeout +3-4 sec than command's.
Also its possible to set this values statically for all scripts within server, via
REGISTRY settings or ODBC settings (if You use this one).
Hope this help.
0
 

Author Comment

by:shellyahoo71
ID: 2685186
I still got the same error, any idea?
Server.ScriptTimeout = 1600
connData.CommandTimeout= 1000
Set objAnswer = Server.CreateObject ("ADODB.Recordset")
0
 
LVL 1

Expert Comment

by:ATM
ID: 2685417
What the execution time within SQL Query Analyzer? How much data you try to select and publish... I hope not 17 Megs? :)
0
Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 

Author Comment

by:shellyahoo71
ID: 2685462
Adjusted points from 50 to 60
0
 

Author Comment

by:shellyahoo71
ID: 2685463
ATM
The stored procedure will search for 2 tables and get the data
There are around 50 fields come out as a result

and the result should not exceed 100 records

I only need several fields at the moment.

The whole file in 17 GB
I am not sure how big of these 2 tables.


0
 
LVL 1

Accepted Solution

by:
ATM earned 60 total points
ID: 2685854
I not understood Your rejection cause, U ask how can You set timeout values, I answer and You reject it ... :(  ... I'm ofended ... ;)
Again please check how musch time Your code must be executed in the SQL server itself, with equal query conditions.
And if possible send ASP code exactly.
0
 

Author Comment

by:shellyahoo71
ID: 2688164
Sorry ATM
don't be offended...   :~(
I really need to solve this problem.
It took about around 60-90 seconds to run the query.
But like I said
I have set

Server.ScriptTimeout = 1600
connData.CommandTimeout= 1000
still got the timeout expired..

Help, help... Help.. :~(
0
 

Author Comment

by:shellyahoo71
ID: 2688175
ATM
Another quick question,
do you think the index of the reference field will help?
0
 
LVL 1

Expert Comment

by:ATM
ID: 2689936
Of course make index for WHERE and ORDER BY fields.
How do You open recordset?
You can create stored procedure which returns recordset. And open it as Recordset:

Set commandObj=Server.CreateObject("ADODB.Command")
commandObj.ActiveConnection=yourConn
commandObj.CommandTimeout=60
commandObj.CommandType=4
commandObj.CommandText="yourSPName"
commandObj.Parameters.Append commandObj.CreateParameter("param1", adInteger,adParamInput,10,some_param)
Set rsObj=commandObj.Execute()

or

Set rsObj=Server.CreateObject("ADODB.Recordset")
rsObj.Cursortype adForwardOnly
rsObj.Open commandObj
....

so to open recordset, commandtimeout will be used.

0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Choose the older file FSO 6 48
Summernote and form validation 10 137
Auto Submit on dropdown box 3 76
ASP CDO - Limiting Connections 2 51
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…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

773 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