471,075 Members | 704 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,075 software developers and data experts.

SQL server 2000 timeout

I have been trying to do a query(view) in Enterprise Manger and Query
Analyzer with a timeout result everytime. In EM in happens in about
30 seconds and in QA it happens in about 45 seconds.

I am running the query directly on a W2003SBS server with SQL Server
2000 (V 8.00.2039 )running on the server.

The message is simply
[Microsoft][ODBC SQL Server Driver]Timeout expired

I have set all the time out properties I have seen and that have been
mentioned in these forums to no avail.

I have tried rewriting several of these queries also to no avail.

I am working with a database of 140 million records and have indexed
all the pertinent fields.

I have seen some info up here about a bug in SQL 2000 that impacts EM
and not QA (Q247070), but I can't QA to work either. I seem to have
no impact on the length of the timeout. The query is not that
complicated, but does go against 140M records. I should be able to
set thetimeout longer to let this run.

Any ideas.

Thanks,
Brad
Jul 3 '06 #1
3 16151
Brad (bm***************@bmegroup.com) writes:
I have been trying to do a query(view) in Enterprise Manger and Query
Analyzer with a timeout result everytime. In EM in happens in about
30 seconds and in QA it happens in about 45 seconds.
I don't think the timeout in Enterprise Manager is controllable.

In Query Analyzer you set it under Tools->Options->Connection->Query Timeout
(in seconds). By default it's 0, which means forever, so you would only
get a timeout if someone has changed it.

If the view refers to remote tables, you could get a timeout for the
remote part of the view, but this timeout is by default 10 minutes.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jul 3 '06 #2
You need to do some tuning...

Erland Sommarskog wrote:
Brad (bm***************@bmegroup.com) writes:
I have been trying to do a query(view) in Enterprise Manger and Query
Analyzer with a timeout result everytime. In EM in happens in about
30 seconds and in QA it happens in about 45 seconds.

I don't think the timeout in Enterprise Manager is controllable.
It is controllable through EM...Server Property box --connexions tab
-->Request timeout in ms...
In Query Analyzer you set it under Tools->Options->Connection->Query Timeout
(in seconds). By default it's 0, which means forever, so you would only
get a timeout if someone has changed it.

If the view refers to remote tables, you could get a timeout for the
remote part of the view, but this timeout is by default 10 minutes.
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jul 4 '06 #3

http://vyaskn.tripod.com/watch_your_timeouts.htm
http://vyaskn.tripod.com/sql_odbc_timeout_expired.htm

Madhivanan
Cimode wrote:
You need to do some tuning...

Erland Sommarskog wrote:
Brad (bm***************@bmegroup.com) writes:
I have been trying to do a query(view) in Enterprise Manger and Query
Analyzer with a timeout result everytime. In EM in happens in about
30 seconds and in QA it happens in about 45 seconds.
I don't think the timeout in Enterprise Manager is controllable.
It is controllable through EM...Server Property box --connexions tab
-->Request timeout in ms...
In Query Analyzer you set it under Tools->Options->Connection->Query Timeout
(in seconds). By default it's 0, which means forever, so you would only
get a timeout if someone has changed it.

If the view refers to remote tables, you could get a timeout for the
remote part of the view, but this timeout is by default 10 minutes.

Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jul 5 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Hasan | last post: by
9 posts views Thread by John | last post: by
9 posts views Thread by Jim | last post: by
8 posts views Thread by BBC1009 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.