locked
OLE DB provider "SQLNCLI10" for linked server ... returned message "Query timeout expired". RRS feed

  • Question

  • I am connecting to a remote sql server 2008 using an oledb linked server.  My queries occasionally timeout with the following error message:

    OLE DB provider "SQLNCLI10" for linked server ... returned message "Query timeout expired".
    Msg 7399, Level 16, State 1, Line 2
    The OLE DB provider "SQLNCLI10" for linked server ... reported an error. Execution terminated by the provider because a resource limit was reached.
    Msg 7320, Level 16, State 2, Line 2

    So I checked the remote query timeout on the remote server, and it is set to the default 600.  Now I was tempted to conclude that thtat was the problem and contact the administrator of the remote server, but something doesn't quite add up.

    A remote query timeout of 600 indicates that queries should timeout after 10 minutes, correct?  Well many of my queries complete successfully in anywhere from 30 minutes to an hour depending on the load.  So if the remote query timeout property was really the issue, shouldn't all of my queries fail after 10 minutes?  Why is it that my long running queries only occasionally fail? 

    Any help would be appriciated.

    Tuesday, May 17, 2011 3:33 PM

Answers

  • Hi ddiamond,

    >>To change how long my linked server queries take to timeout, do I need to change this setting on my server or on the remote server?

    The error messages might occur when a query exceeds the timeout option values. You can try to 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, please refer to the KB article addressing this type of issue: http://support.microsoft.com/kb/314530.

    Also take a look at another KB article: http://support.microsoft.com/kb/270119.

     


    Best Regards,
    Stephanie Lv


    • Marked as answer by Stephanie Lv Friday, May 27, 2011 8:37 AM
    Thursday, May 19, 2011 7:40 AM
  • >>To change how long my linked server queries take to timeout, do I need to change this setting on my server or on the remote server?

    Since this part wasn't answered.  It needs to change on the local server.  Making the change only on the remote server still timed out at 10 minutes.

    Although, I didn't reset the options on the remote server when I changed the local server, so they are on both.

    • Marked as answer by ddiamond Monday, February 13, 2012 7:34 PM
    Monday, February 6, 2012 4:22 PM

All replies

  • One more question.  MSDN states:

    Remote query timeout (in seconds, 0 = no timeout) 
    Specifies how long (in seconds) a remote operation may take before SQL Server times out. The default is 600 seconds, or a 10-minute wait.
    
    

    To change how long my linked server queries take to timeout, do I need to change this setting on my server or on the remote server?

    Tuesday, May 17, 2011 7:29 PM
  • Hi ddiamond,

    >>To change how long my linked server queries take to timeout, do I need to change this setting on my server or on the remote server?

    The error messages might occur when a query exceeds the timeout option values. You can try to 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, please refer to the KB article addressing this type of issue: http://support.microsoft.com/kb/314530.

    Also take a look at another KB article: http://support.microsoft.com/kb/270119.

     


    Best Regards,
    Stephanie Lv


    • Marked as answer by Stephanie Lv Friday, May 27, 2011 8:37 AM
    Thursday, May 19, 2011 7:40 AM
  • >>To change how long my linked server queries take to timeout, do I need to change this setting on my server or on the remote server?

    Since this part wasn't answered.  It needs to change on the local server.  Making the change only on the remote server still timed out at 10 minutes.

    Although, I didn't reset the options on the remote server when I changed the local server, so they are on both.

    • Marked as answer by ddiamond Monday, February 13, 2012 7:34 PM
    Monday, February 6, 2012 4:22 PM