2006/09/14

Remote query timeout

If you are using linked servers and encounter the following error:

OLE DB provider "SQLNCLI" for linked server "SRV_2000" returned message "Query timeout expired".

check your remote query timeout setting.

To do it execute this query:
  sp_configure 'remote query timeout (s)'

or go to:
SQL Server 2000 Enterprise Manager
(if you're lucky enough to still be using SQL Server 2000 Enterprise Manager)

or to:
SQL Server 2005 Management Studio
(if you have to use SQL Server 2005 Management Studio, that failed to accomplish such an easy task - not being worse that Enterprise Manager - I'll rant about this more someday)

It is also worth remembering that if you are executing a remote query in a step of a job and this fails because of the timeout, the step will be written to job's history with status 'Successful':

Executed as user: DOMAIN1\sqlagent2005. OLE DB provider "SQLNCLI" for linked server "SRV_2000" returned message "Query timeout expired".
[SQLSTATE 01000] (Message 7412). The step succeeded.

4 comments:

  1. Hi There,

    I followed your steps and made sure that the remote connections timeout period was set to '0' and I am still getting the message:

    OLE DB provider "SQLNCLI" for linked server "LINKED_SERVER" returned message "Query timeout expired". [SQLSTATE 01000] (Message 7412). The step succeeded.

    It happen at exactly 10 minutes.
    Is there anything else that can be done?

    thanks

    ReplyDelete
  2. I don't no idea what may be wrong. You can try to change the timeout to something else than 0 (for example, 60 or 120) to see if the time after you query times out is different than 10 minutes (respectively 1 minute or 2 minutes). If it does, you can set the timeout period to some large value.

    Aren't you changing the timeout period on wrong server?

    ReplyDelete
  3. I found the issue... the timeout was occurring on my 'source' server and not my 'destination' server.
    I made sure that the timeout for remote connections was right on the 'destination' of my linked server but, I didn't even think to check the 'source' server or initiating server and it was set exactly to 10mins...

    Thanks again!!

    ReplyDelete