Query Timing out in Enterprise Manager

I am running Enterprise Manager in SQL Server 2000 and attempting
to do some table maintenance. When I run a delete query that should
delete approx. 600,000 rows, the query timesout and gives the following
message "[Microsoft][ODBC SQL Server Driver] Timeout expired.", but
when I run the same query using "SQL Query Analyzer" the query runs
for 4-5 minutes and finishes with no issues.
What is the difference between running the query in Query Analyzer and
Enterprise Manager?
Here is the query:
Delete from tablename
where (fieldname like '155%')
Enterprise Manager was designed as more of an admin tool,
not a client data tool. It's not meant to be used for long
running queries. Microsoft has a KB article regarding
timeouts with Enterprise Manager. The workaround is to use
Query Analyzer for long running queries:
http://support.microsoft.com/?id=247070
-Sue
On Thu, 22 Mar 2007 07:15:31 -0700, keith c
<keithc@.discussions.microsoft.com> wrote:

>I am running Enterprise Manager in SQL Server 2000 and attempting
>to do some table maintenance. When I run a delete query that should
>delete approx. 600,000 rows, the query timesout and gives the following
>message "[Microsoft][ODBC SQL Server Driver] Timeout expired.", but
>when I run the same query using "SQL Query Analyzer" the query runs
>for 4-5 minutes and finishes with no issues.
>What is the difference between running the query in Query Analyzer and
>Enterprise Manager?
>Here is the query:
> Delete from tablename
> where (fieldname like '155%')
|||rofl
WOW... I had no idea that they had a write up for that
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:msf703tleevcrihl3pfvsnljvhi5u5ac1t@.4ax.com...
> Enterprise Manager was designed as more of an admin tool,
> not a client data tool. It's not meant to be used for long
> running queries. Microsoft has a KB article regarding
> timeouts with Enterprise Manager. The workaround is to use
> Query Analyzer for long running queries:
> http://support.microsoft.com/?id=247070
> -Sue
> On Thu, 22 Mar 2007 07:15:31 -0700, keith c
> <keithc@.discussions.microsoft.com> wrote:
>

Query Timeout Setting

SQL Server 2005 is timing out when running loooong queries.

Is there a global place I can extend the timeout for queries? I do not mean in code but just in general use?

Thanks

Craig

Perhaps its better to fix the long running query?

|||

ndinakar:

Perhaps its better to fix the long running query?

I agree completely!

The number of reusable connections is limited. A few users of a single long-running query can bring an entire system down!

Think about driving on a road with ten lanes going your way. Just two slooooow drivers, each in different lanes, can cause traffic jams really fast in busy traffic.

|||

When you have 80 - 160 million records in 2 or more tables we need all options possible, so any answers to the original question?

|||

80-160 mill rows is nothing for SQL Server, if your query is properly optimized and have the right indexes. I work with tables over 500 mill rows to as high as 1.2 bill rows. So, fixing the query will be a better solution in the longer run. If you can show us the query that's timing out we can try to help with that.

Check this article on various ways to set timeout limits:http://vyaskn.tripod.com/watch_your_timeouts.htm

|||

Both the connection object and the command object have a property that deals with maximum times.

For legitimate, long-running queries that cannot be better tuned or designed, just up the time on those properties.

That way, you force your developers (assuming you are developing with test datasets of the appropriate size) to identify long-running queries before they hit production. It gives you an opportunity to improve the sql before the system goes live.

By searching for those property names, you can identify long-running queries (from the developer's point of view) and route them to your sql specialist for tuning.

Query timeout problems

Front End: Access 2000 Project (.adp)
Back End: SQL Server 2000

I have an application that keeps timing out. I have opened the
DataLink properties in the front end (File, Connection, Advanced tab)
and set the timeout to 999. But the connection still occasionally
times out.

Any ideas? Some of the SQL is pretty horrible (multiple sub-queries
etc.)

TIA

Edward
--
The reading group's reading group:
http://www.bookgroup.org.uk"Edward" <teddysnips@.hotmail.com> wrote in message
news:25080b60.0501310730.3f8e6ed9@.posting.google.c om...
> Front End: Access 2000 Project (.adp)
> Back End: SQL Server 2000
> I have an application that keeps timing out. I have opened the
> DataLink properties in the front end (File, Connection, Advanced tab)
> and set the timeout to 999. But the connection still occasionally
> times out.
> Any ideas? Some of the SQL is pretty horrible (multiple sub-queries
> etc.)
> TIA
> Edward
> --
> The reading group's reading group:
> http://www.bookgroup.org.uk

The timeout you changed is the connection timeout, so it won't affect
timeouts caused by long-running queries. Depending on what connection
library you're using (ODBC, ADO), you should be able to change the general
timeout value, but I have no idea how - perhaps an Access group might give
more help.

In ay event, if the timeouts occur during different queries, or the same
queries with different parameters, you'll probably have to try to identify
the problem queries more exactly - Profiler would be a good place to start.

Simon