February 11, 2009 at 10:24 am
OLE DB provider "SQLNCLI" for linked server "Rev3" returned message "Query timeout expired".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "Rev3" reported an error. Execution terminated by the provider because a resource limit was reached.
Msg 7421, Level 16, State 2, Line 1
Cannot fetch the rowset from OLE DB provider "SQLNCLI" for linked server "Rev3".
I am getting this error after running the query for 25min though the time out was set to " 0 " on the Link.
I am importing data from a link server Rev3
February 13, 2009 at 3:28 am
Information about Msg 7399, Level 16, State 1, Line 1 from Microsoft knowledge base ( http://support.microsoft.com/kb/314530 ) :
"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."
"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. "
"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
"
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
February 13, 2009 at 6:55 am
When running the query, there are other things you can do too.
1) Use Profiler on the destination server to see if anything's actually making it to the server
2) Use sp_who2 to check blocking
3) Use the GUI, navigate to Management -> Activity Monitoring on both servers to check for blocks & processes
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply