Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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

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

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

↑ Back to the top

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.

↑ Back to the top

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

↑ Back to the top

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

↑ Back to the top

Keywords: kb, kbbillprodsweep, kbprb, kbappliestoyukon, kbyukonsweep

↑ Back to the top

Article Info
Article ID : 314530
Revision : 5
Created on : 8/19/2020
Published on : 8/20/2020
Exists online : False
Views : 420