Sql-server – Catch OLE DB provider “SQLNCLI10” for linked server “[Some Server]” returned message “Query timeout expired”

linked-serverquery-timeoutsql serversql-server-2008-r2sql-server-2016

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.

Best Answer

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.

Related Question