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

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

Solutions:

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 

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

For more information please refer microsoft KB article.