Go Premium for a chance to win a PS4. Enter to Win


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

Posted on 2014-10-31
Medium Priority
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

782 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