Re: Help: inappropriate OLEDB timeout:

Matt C. (canimal@xxxxxxxxxxx) writes:
> Starting yesterday, an application that has previously run without
> problems (for several weeks now) has started throwing Timeout Expired
> errors after 30 seconds.
>
> The Command.CommandTimeout, Connection.CommmandTimeout, and
> Connection.ConnectionTimeout on the client are all set properly to values
> well in excess of 30 seconds. Also, the Connection object is using
> adUseServer for cursors.
>
> The application was developed using the SQLOLEDB provider. I have made a
> test version using the MSDASQL.1 provider, and this test version seems to
> be running OK. So there is some problem with the SQLOLEDB provider,
> apparently. (Using MSDASQL for testing is fine, but I do not want to
> switch db providers permanently unless there is no other choice.)
>
> I tried upgrading to MDAC 2.8 SP1, this didn't help. I have checked for
> lock timeouts, nope, not the problem.
>
> I am baffled. Does anyone have any ideas?

It's difficult to have any ideas about an application that I don't anything
about. All I know is that a query starts to time out after 30 seconds.
There could be plenty of reasons for this.

It could be that the data volume in the database has grown, so that the
query now takes longer time to run. Note that because of changes in
statistics, the optimizer may settle on a different query plan - most
often this is to the better, but not always.

But it could be blocking problems. It could be another process that
blocks you. But - with ADO it's possible to block yourself. This
can happen if you issue a query on a connection that is busy fetching
data from a result set. An ODBC driver or DB-Library would tell you that
the connection is busy. But ADO tries to be a nice guy and opens a
second connection behind your back. Unfortunately, SQL Server does not
know that the connections are the same, you they block each other.

If nothing of this helps you, you will need to post more information.

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
.

Relevant Pages

  • Re: Query from ms sql server question
    ... For example, in my worksheet, there a cell which user will enter the production number and macro will perform an automatic connection to sql server and query the table base on the production number and retrieve the data base on the production alone and place it on the excel sheet. ...
    (microsoft.public.excel.programming)
  • Re: ANSI_WARNINGS error in Stored Procedure
    ... The error says to set these options for your _connection_ not for the query. ... > I have a linked Server on my regular SQL server, and when I try to create ... a stored procedure that reads from the linked server, ...
    (microsoft.public.sqlserver.security)
  • Re: Pivot Table Timeout
    ... If the query takes a long time and times-out, it can be adjusted with properties in the SQL connection string. ... 'Open Connection to remote SQL Server ...
    (microsoft.public.office.developer.web.components)
  • Re: Pivot Table Timeout
    ... If the query takes a long time and times-out, it can be adjusted with properties in the SQL connection string. ... 'Open Connection to remote SQL Server ...
    (microsoft.public.office.developer.web.components)
  • Re: Problem with blank entries being filled with other values
    ... We have a table in SQL Server ... We run a Make Table query within Access, which produces a table in Access ... The connection via DDE worked. ... >> in the Counter field are replaced with values from this field in other ...
    (microsoft.public.word.mailmerge.fields)