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.