Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

[Microsoft][ODBC SQL Server Driver]Timeout expired

Posted on 2003-10-28
9
Medium Priority
?
133,469 Views
Last Modified: 2011-08-18
When I run an online report sometimes I get the following error pointing to my ASP page
How can I stop this from happening....This happens when the user requests lots of data.
Is there any way to make the time unlimited?


Microsoft OLE DB Provider for ODBC Drivers (0x80040E31)
[Microsoft][ODBC SQL Server Driver]Timeout expired
0
Comment
Question by:AutomaticSlim
[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
9 Comments
 
LVL 15

Expert Comment

by:SRigney
ID: 9635536
There is a connection timeout and a CommandTimeout property that both have default values of 60 seconds.  You probably need to modify the CommandTimeout to be longer.
0
 

Author Comment

by:AutomaticSlim
ID: 9635731
How do I do this?
0
 
LVL 9

Accepted Solution

by:
apirnia earned 280 total points
ID: 9636001

objConn=Server.CreateObject ("ADODB.connection")
  objConn.ConnectionTimeout = 0
  objConn.CommandTimeout = 0
objConn.open

This should do....

There is another place in IIS to change timeouts but I think your problem is this one.....
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 35

Expert Comment

by:David Todd
ID: 9636017
Hi,

I'd recommend _against_ making it unlimited.

Try timing it and then set the timeout to twice the longest time you get. The timeout is in there for a reason.

Alternatively, reconsider how much data you let the user select in any one report or batch.

Maybe both are needed.

Regards
  David
0
 
LVL 9

Expert Comment

by:apirnia
ID: 9636117
Yes true.... above is just a code for what you need to do....... use 0 just for testing.

In a simple description the reason the Timeout is there is because some end users don't have patience. They see that the report is taking a long time so they click on refresh again and again not knowing that it is has lots of data. This backs up the SQL server. and when you have it set to 0 it will run all the way. When you have Timeout it kills it if it is taking to long
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 9636477
Those solutions should help with the actual timeout problem.

I would suggest spending some time and looking at the underlying cause of the timeout problem. Look at the performance of your server, look at the amount of data being sent, look at the way in which the data is being rounded up. Are your queries the most efficient, is there a lot of activity on your server? How are your indexes arranged, is there another way to write your queries, etc. etc.

Increasing the timeout will just stop the timeout from happening after the default, but your users will still have to wait that much time for the report to generate, 60 seconds is a lifetime to some users out there ;)
0
 

Expert Comment

by:anok999
ID: 11230490
My problem is even in sql environment i.e right click on table goto query and run some simpale qry like col03=so and so even then i am getting ODBC Driver time out for SQL
0
 

Expert Comment

by:oakes3
ID: 12957006
I don't know if you've solved this yet - but I just had the same problem on an Access XP form hitting a SQL 2000 box.  Only one table was timing out, and only on updates and inserts (the data displayed OK on the form)...

Update on linked table "tblCustomer" failed.  [ODBC SQL Server Driver] Timeout Expired (0)"

Recreating the table from scratch, a little bit at a time, uncovered the problem: three (3) ntext fields.  Changing these to nvarchar(4000) solved the problem.  Changing them back to ntext confirmed that this was indeed the issue.  

0
 

Expert Comment

by:xstaxjke
ID: 13573964
I had a similar issue but with a much smaller database.

In the end I had to copy the database to a new name and update my site accordingly. Apparently the table was somehow damaged. I had already tried compact and repair and it made no difference. This is probably not a guaranteed fix but using the answer above only caused the page to never load.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

688 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