Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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.

688 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