Knowledge Base

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

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 "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', 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

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).

Additional query words: OPENQUERY OPENDATASET OPENROWSET linked server distributed query

Keywords: kbprb KB314530

Microsoft KB Archive/314530

Contents