2

Without changing the settings on servers, etc to the linked server connections, etc, is there a way to catch the dreaded?

"OLE DB provider "SQLNCLI10" for linked server "[Some Server here]" returned message "Query timeout expired""

"OLE DB provider "SQLNCLI11" for linked server "[Some Server here]" returned message "Query timeout expired""

and either have the procedure retry to run or continue on to the next process in a cursor?

We have a dynamic SQL Server system set up to call procedures based on a specific process we're running. This works fine but every once in a while, one of the steps will throw the above error and of course a traditional Try...Catch block doesn't capture it.

I've searched for several hours on this and I need a way to be able to "capture" this error and either retry the procedure or skip the procedure but not exit the cursor (so go to the next process in line). We have a 2016 and a 2008 R2 servers communicating with each other.

Right now we have a single cursor running with 20 such steps in it (we are trying to use this as a job task replacement) but eventually we will have 30 or 40 processes, with tasks totaling well into the hundreds using this system.

Please let me know if you have any ideas.

0

Simply reconfigure the Linked Server to have no timeout. Or at least a longer timeout value.

EXEC master.dbo.sp_serveroption @server = N'<server_name>'
    , @optname = N'query timeout'
    , @optvalue = N'1200';  --1200 second timeout

To configure the server so that no linked servers ever timeout, make this change:

EXEC sys.sp_configure 'remote query timeout', 0; 

Then ensure the linked server has sp_serveroption 'query timeout' is set to 0.

  • That's what we're trying to avoid. I'd be hung out to dry if I changed these settings. All I'm trying to do is figure out a way to determine when this happens and catch it real time so we have some way to alert us of it (outside of it just dropping out of the procedure it's working on). I'm trying an inner / outer try catch loop now but of course everything worked today so I'm not sure that will help. – Ron Jun 4 at 18:09
  • You can run WAITFOR DELAY '00:20:00'; against the linked server to generate a timeout at will. – Max Vernon Jun 5 at 20:18
  • Even with that, the specific procedure normally takes 30 seconds or less. They have backups and maintenance cycles running on the server. Since it kicks me back to my main procedure, I think what I will do is use the WaitFor Delay and have it go back in and try again after 5 mins or so. Figure MS would have figured this one out by now. Thanks for the ideas Max. I did upvote you but I'm a newbie here so it doesn't record it. – Ron Jun 7 at 12:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.