Timing out message when running as stored procedure on a linked server (MS SQL Servers)

Posted on 2014-10-31
Last Modified: 2014-11-06

I am having trouble with a timing out issue when running a stored procedure on a linked server. The error message returned is:
OLE DB provider "SQLNCLI" for linked server "Newreg" returned message "Query timeout expired"
The calling server is running SQL Server 2005 on a Windows 2003 server.
The linker server (Newreg) is running SQL Server 2008 R2 on a Windows 2008 R2 Enterprise server.

I have Googled the error and the made the obvious changes to the various timing out settings on both servers, i.e. setting no limit by entering zeros (see attachment). I have also restarted SQL Server on both machines (but not restarted Windows).

However, none of the above work.

The stored procedure has been modified and now takes 17 minutes to run, compared to before, when it took 5 minutes. The remote call worked when it took 5 minutes, but not when it takes 17 minutes. Another similar stored procedure, which now takes only about 4 minutes to run still works when called remotely. This seems consistent with a timing out problem, but maybe I'm missing other possibilities.

I would be grateful for suggestions on where to look next, etc.

Richard Greathead
Question by:smucRJG2
  • 3
  • 2
LVL 35

Expert Comment

by:David Todd
ID: 40416867

If you ran the 17min proc directly on the server instead of via the link would it still take 17mins to run? Are there steps which can be put into a second procedure? That is, call one proc with the result returned, call the second prod ...


Author Comment

ID: 40417097

If I run the procedure on the remote server (Newreg) it runs in about 15 minutes.

The remote procedure is called from a procedure on the calling server that uses the data returned from the remote procedure and then deletes the audit data created on the remote server (the remote procedure services a number of applications and creates audit records which are not needed in this particular case). It also calls another procedure on the remote server successfully (before the problem procedure is called). So I am already dividing up the work needed on the remote server into smaller steps and would find it impractical to further sub-divide.

However, I have applied more time checks in the calling procedure and it shows that the error message is occurring exactly 10 minutes after the call to the miscreant remote procedure is made. Ten minute, 600 second, is the default timeout setting, so it looks as though the new (0) timeout settings I have applied everywhere I am aware of are being ignored.

Do you think I need restart the servers themselves? I am reluctant to do this because of the disruption this will cause. Are there any other timeout settings I might have missed, or are there any problems working with a mixture of 2005 and 2008 server versions? Or???

Thank you for looking into this,

LVL 35

Expert Comment

by:David Todd
ID: 40417504

There is a connect timeout and a command (or query) timeout. Don't that these timeouts can occur on the client or on the server, and as you are doing this over a linked server, both client and server side of that as well.


PS I like on well connected servers to set timeout for 5secs. That way I don't have to wait the 30secs for the timeout if I mis-typed something.

Accepted Solution

smucRJG2 earned 0 total points
ID: 40417676
In the end, I discovered that if I set the Remote Query Timeout on the calling server to '0' the problem is solved (Server Properties screen). The checkbox immediately above where this is set is to 'Allow Remote Query Connections to this Server', so I would have thought the time out referred to was for calls coming in to the server, not vice-versa. Maybe it's just a badly designed screen? In fact if I set then set the Query timeout to 600 on the remote server it still worked. Seems back to front, but it works.

Author Closing Comment

ID: 40425771
In the end, I found my own solution, but talking to the expert did make me think along different lines, so it was worthwhile. Thanks

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now