[Microsoft][ODBC SQL Server Driver]Timeout expired

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
AutomaticSlimAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SRigneyCommented:
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.
AutomaticSlimAuthor Commented:
How do I do this?
apirniaCommented:

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

David ToddSenior Database AdministratorCommented:
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
apirniaCommented:
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
MikeWalshCommented:
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 ;)
anok999Commented:
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
oakes3Commented:
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.  

xstaxjkeCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.