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:
(if you're lucky enough to still be using SQL Server 2000 Enterprise Manager)
or to:
(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.
Hi There,
ReplyDeleteI 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
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.
ReplyDeleteAren't you changing the timeout period on wrong server?
I found the issue... the timeout was occurring on my 'source' server and not my 'destination' server.
ReplyDeleteI 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!!
Glad I could help!
ReplyDelete