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.

1 Answer 1

1

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.

1
  • 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, 2019 at 18:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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