When running a query in SQL Server 2000, I got "Timeout expired" error. How
can I increase the timeout time ?
Thanks.

fniles wrote:
Are you running this query in Query Analyzer or from some other
application or web page?  Does it cross a linked server?

Thank you all.
I am running the query from the Enterprise Manager, right click on the
table, open table, query.
The table is big, it has 16,043,855 records.

The query is the following
select *
FROM         tblA
WHERE     (Symbol = 'esu6') AND ([Date] > '08/10/06 8:25:00') and   ([Date]
< '08/10/06 8:50:00')
The index is not on the symbol and not on the Date.

Since I am running the query from the Enterprise Manager,besides putting an
index on the Date column, how can I increase the timeout time ?

Thanks.

Show quoteHide quote

fniles wrote:
Show quoteHide quoteFirst, you should stop using Enterprise Manager to run queries, that's
not what it's designed for, and it doesn't do it well.  Use Query
Analyzer for running queries and viewing data.

Second, since the two fields that you're filtering on are not indexed,
your query is going to do a scan of the table, which is usually the
least-desired method of access.  An added benefit of running queries in
Query Analyzer is that you can view the execution plan that the query
will follow, and you'll see the table scan taking place.

Thank you.
But, sometimes I need to run queries from Enterprise Manager, for example
when  I need to change a value, etc.
I will revise the query to use the indexed column.
But, out of curiousity, is there any way to increase the timeout time in
Enterprise Manager or Query Analyzer ?

Thanks.

Show quoteHide quote

fniles wrote:
Nope, not true, you NEVER need to run queries from Enterprise Manager.
You're trying to use it like Excel, which it is NOT.  Use UPDATE queries
in Query Analyzer when you need to modify data, INSERT queries to add
new data, SELECT queries to view data.  There is no legitimate reason to
EVER run queries inside Enterprise Manager.  Break away from the
pointy-clicky stuff and learn to do things the right way.

For a simple update on a non-critical system, I'd say it usually doesn't
make a lot of difference whether QA or EM is used.
For a critical system I'd expect all data changes to be made via a client
application so any manual data changes would be very much the exception. If
I have to do these, I use QA as Tracy recommends because the functionality
is different - I usually prefix an update query in QA with BEGIN
TRANSACTION, and then only COMMIT afterwards if I'm happy with the
rowcount/messages returned. You only need this to be relevant once to see
why it's good to do :) Also, on these systems I usually like to keep some
sort of log of this type of change, the time done, reason etc.
  Cheers,
        Paul Ibison SQL Server MVP, www.replicationanswers.com

Query Timeout settings:
In QA - tools, options, connections
In EM - tools options, advanced
  Cheers,
        Paul Ibison SQL Server MVP, www.replicationanswers.com

Paul, how about if you're calling the SP from VB6?
I'm also getting a "Timeout Expired" error message. It doesn't happen very
often but it does happen. The table that's being queried only has about 20
rows.

Thanks,
Rita

Show quoteHide quote

RitaG wrote:
You can set the timeout on your data connection within VB...  The
default is 30 seconds I believe...

Thanks Tracy.
My code is currently using an ADODB.Recordset where the CommandTimeout
property is set for the Connection object.

Dim aConn As ADODB.Connection

' Get the connection object
Set aConn = GetConnectionObject(ServerName, DatabaseName, UID, Password)
aConn.CommandTimeout = 0

' Execute the Stored Procedure
Set GetRecord = aConn.Execute(StoredProcName, lRecsAffected,adCmdStoredProc)

Thanks,
Rita

Show quoteHide quote

In EM, look at tools, options, advanced for the timeout setting.
You'll also need a composite index on Symbol and Date, with the most
selective of the two defined first.
If a lot of results are likely to be returned, and assuming you just want to
look at a sample, you might also want to use TOP to retrict to the first 100
or so.
   Cheers,
        Paul Ibison SQL Server MVP, www.replicationanswers.com

Hi
I think you need to start optimizing the query. Do you have a WHERE clause ?
Do you have any indexes defined on the tables?
What does the query do? How big is an output?









Show quoteHide quote

A timeout occurs on the client so the specification details depend on the
database API you are using in your application code.  In the case of ADO and
ADO.NET, there is a CommandTimeout property of the command object that
defaults to 30 seconds.  A specification of 0 means infinite.

Show quoteHide quote

Hi,

Take a look into the article in below URL.

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

By default the ADO connections timeouts in 30 seconds. Probably you can try
increasing the connection time out (.CommandTimeout)
to 40 seconds or so and try....

Thanks
hari
SQL Server MVP

Show quoteHide quote

Just add "Connect Timeout=30" in the connection string of sql server.The
time is in seconds.You can set the time according to your need.



*** Sent via Developersdex http://www.developersdex.com ***

On Tue, 05 Sep 2006 00:12:37 -0700, Kunal Tembhurnekar
<kun***@hexaware.com> wrote:
in <urGnrqL0GHA.2***@TK2MSFTNGP06.phx.gbl>

Have you actually tried that?  It has no apparent effect.  Neither does
specifying the ConnectionTimeout property of the connection object. This
problem appears to be specific to SQL Server since it works as
advertised with PostgreSQL.

---
Stefan Berglund

Stefan Berglund wrote:
Show quoteHide quoteConnectionTimeout defines the length of time to wait for a successful
CONNECTION.  CommandTimeout defines the length of time to wait for
results from a COMMAND.

On Tue, 05 Sep 2006 13:43:29 -0500, Tracy McKibben
<tr***@realsqlguy.com> wrote:
in <44FDC551.7050***@realsqlguy.com>

Show quoteHide quoteYes sir, I'm aware of that distinction.  Thank you for clarifying the
distinction.  It still has no bearing on the fact that employing it is
to no avail when using ADO.  It works as intended when connecting to
PostgreSQL but not to SQL Server.

---
This posting is provided "AS IS" with no warranties and no guarantees either express or implied.

Stefan Berglund

Stefan Berglund (sorry.no.kool***@for.me) writes:
You should set the CommandTimeout, which you can set on either the
Connection object or the Command object if memory serves.

And the issue has nothing to do what so ever with SQL Server, since SQL
Server does not no what a commend timeout is. This is a client-side
(mis)feature.

The only time you can get a command timeout within SQL Server is when
you run a distributed query, in which case the access to the linked
server can time out. SQL Server is here employing an OLE DB provider for
the access, and it's the OLE DB provider that reports the timeout.

On Sat, 9 Sep 2006 09:28:00 +0000 (UTC), Erland Sommarskog
<esq***@sommarskog.se> wrote:
in <Xns983974A4EB8E4Yazorman@127.0.0.1>

Show quoteHide quoteI'm not sure why you've transmogrified this into a command timeout
issue.  I want to be able to set a connection timeout which I interpret
as the amount of time I'm willing to let elapse without making a
successful connection.

Using Visual Basic 6 and changing only the connection string I'm able to
make use of the ConnectionTimeout property of the ADO connection object
when connecting to PostgreSQL but not when connecting to SQL Server.
Thus my statement that it must be SQL Server.  Obviously there is a lot
of code between my client app and the database server but everything
else being equal the ~connection timeout~ property is ineffective with
SQL Server and effective with PostgreSQL when implemented from a
VB6/OLEDB client.

---
Stefan Berglund

Stefan Berglund (sorry.no.kool***@for.me) writes:
Probably, because the post that start this thread said:

  When running a query in SQL Server 2000, I got "Timeout expired" error.   
  How can I increase the timeout time ?

This clearly talks about a command timeout.

It must be SQL Server? But if you get a connection timeout, you don't
even make it to SQL Server...

Of course, it could be an issue in the OLE DB provider for SQL Server.
However, I tried using the Connect Timeout in a connection string
when connecting to a non-existing SQL Server instance. I tried this
with SQLNCLI, SQOLEDB and MSDASQL + SQL Server ODBC Driver. In all
three cases the Connect Timeout in the connection string clearly
had an effect.

On Mon, 11 Sep 2006 21:55:41 +0000 (UTC), Erland Sommarskog
<esq***@sommarskog.se> wrote:
in <Xns983BF3690BA80Yazorman@127.0.0.1>

You're right that it's not SQL Server.  It must be ADO since this
property exists so that ADO can give up and return to the client when
this limit is reached.

Hmm.  Very odd.  It very clearly ignores my setting and consistently
takes 42 seconds for the first timeout to occur and 12 seconds for the
second using the following VB code.  I used a server name without the
instancename to cause the connection failure.  When I change to the
connect string to "Provider=PostgreSQL;User Id=postgres;Password=" &
msSAPWD & ";Location=showtime;Data Source=" the connectiontimeout
property setting is honored.


Public Const mcsSQLConnectionString = "Provider=sqloledb;Integrated
Security=SSPI;Network Library=dbmssocn;Data Source="


Public Function ConnectSQLServer(ByRef cn As ADODB.Connection, ByVal
sServer As String) As Boolean

  On Error Resume Next
  With cn
    If ((.State And adStateOpen) <> 0) Then .Close
    .ConnectionString = mcsSQLConnectionString & sServer
    .ConnectionTimeout = 5
    .Open
    If (Err.Number <> 0) Then
      Err.Clear
      .ConnectionString = "Provider=sqloledb;User Id=sa;Password=" &
msSAPWD & ";Data Source=" & sServer & ";"
      .ConnectionTimeout = 5
      .Open
    End If
  End With

  ConnectSQLServer = Err.Number = 0
  If (Err.Number <> 0) Then MsgBox Err.Description
  cn.Execute "SET XACT_ABORT ON", , adCmdText Or adExecuteNoRecords

End Function

---
Stefan Berglund

Stefan Berglund (sorry.no.kool***@for.me) writes:
I tried your script, in case the problem was that setting the property
does not work, but it took about 8 seconds the first time, and 5
second time the other.

As server I used a nonsense name. I don't know if matters that you
use a machine that exists, but does not have a default instance.

On Tue, 12 Sep 2006 22:05:53 +0000 (UTC), Erland Sommarskog
<esq***@sommarskog.se> wrote:
in <Xns983DFF91CDFYazorman@127.0.0.1>

Show quoteHide quoteThank you.  That was it.  When the machine has no default instance then
the behavior is as though the connection timeout is ignored.  If the
machine has a default instance and you attempt to connect to a non
existent named instance or a nonsense/nonexistent machine then it works
as advertised.

---
Stefan Berglund

Optimize this query
TOP 50 invoices by company
Denormalizing a database table
How can I insert a new field between existing field?
2005, Ambiguous column name, and ORDER BY