You are currently offline, waiting for your internet to reconnect

Error message when you execute a linked server query in SQL Server: "Timeout Expired"

Extended support for SQL Server 2005 ends on April 12, 2016

If you are still running SQL Server 2005 after April 12, 2016, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

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 1OLE DB provider 'SQLOLEDB' reported an error.[OLE/DB provider returned Timeout expired]
-or-
Server: Msg 7399, Level 16, State 1, Line 1OLE 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 1OLE 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 1OLE 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 "More Information" 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', 30go 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 					

REFERENCES

For more information about the remote login timeout setting and where IDBInitialize::Initialize is called, refer to the "Connecting to an OLE DB Provider" topic in MSDN or the Microsoft SQL Server 7.0 Resource Guide in the BackOffice Resource Kit.

You may also refer to the following topics in MSDN for a description of how the query processor interacts with an OLE DB provider to enable distributed and heterogeneous queries:
  • Microsoft SQL Server 2000 Distributed Queries: OLE DB Connectivity
  • Basic OLE DB
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).
OPENQUERY OPENDATASET OPENROWSET linked server distributed query
Properties

Article ID: 314530 - Last Review: 12/29/2005 04:06:48 - Revision: 4.3

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

  • kbprb KB314530
Feedback