Microsoft KB Archive/314530

= Error message when you execute a linked server query in SQL Server: &quot;Timeout Expired&quot; =

Article ID: 314530

Article Last Modified on 12/29/2005

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2005 Standard Edition
 * Microsoft SQL Server 2005 Developer Edition
 * Microsoft SQL Server 2005 Enterprise Edition
 * Microsoft SQL Server 2005 Express Edition
 * Microsoft SQL Server 2005 Workgroup Edition

-



This article was previously published under Q314530



SYMPTOMS
You may see either one of the following error messages when you execute a linked server query:

Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB' reported an error. [OLE/DB provider returned Timeout expired]

-or-

Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB' reported an error. Execution terminated by the provider because a resource limit was reached. [OLE/DB provider returned message: Timeout expired]

Error 7399 is a generic error message that the provider returns, which indicates there is some sort of problem. You must use trace flag 7300 to get a more detailed and useful error message from the provider. The output from trace flag 7300 can help you to determine if this article covers the specific 7399 error message that you receive.

If you execute a DBCC TRACEON (7300, 3604) statement, and you then execute the query, you may see additional information in the error message; however, whether or not you see more information depends on the provider you use. For example:

Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB' reported an error. [OLE/DB provider returned message: Timeout expired] OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize returned 0x80004005: ].

-or-

Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB' reported an error. Execution terminated by the provider because a resource limit was reached. [OLE/DB provider returned message: Timeout expired] OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute returned 0x80040e31: Execution terminated by the provider because a resource limit was reached.].



CAUSE
There are two configurable timeout options that affect the execution of remote queries. The error messages occur when a query exceeds the timeout option values. Refer to the &quot;More Information&quot; section of this article for further details about the timeout options.



WORKAROUND
To work around this, you can reconfigure the timeout setting.

Based on which type of error you encounter, you can reconfigure the timeout setting as follows:   Set the remote login timeout to 30 seconds, by using this code: sp_configure 'remote login timeout', 30 go reconfigure with override go   Set the remote query timeout to 0 (infinite wait), by using this code:

sp_configure 'remote query timeout', 0 go reconfigure with override go 

MORE INFORMATION
The first error message pertains to the IDBInitialize:Initialize method.

When the query attempts to establish a connection to the remote server, the first error message occurs if the time it takes the query to establish a connection exceeds the remote login timeout option value.

The second error message pertains to the ICommandText::Execute method.

This messages indicates that the query took more time to process than the time that is specified in the remote query timeout configuration setting.

By default, in Microsoft SQL Server 7.0, the timeout setting is zero (0 - infinite wait). By default, in SQL Server 2000 and in SQL Server 2005, the timeout setting is 600 (10 minutes).

Additional query words: OPENQUERY OPENDATASET OPENROWSET linked server distributed query

Keywords: kbprb KB314530

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.