Detecting RPC timeouts

A known SQL server issue is that RPCs that time out do not return an error. This article describes a workaround for that problem.

RPCs made to linked servers must return within a time specified by the remote query timeout configuration option. By default this is set to 600s (10 minutes).

When an RPC times out, a warning is returned to the client (i.e. not an error), so processing of a batch proceeds beyond the (failed) RPC without interruption.

When an RPC call times out, no return status is produced, as if a NULL was returned (assigning the return status to an already-assigned variable will remove the contents of that variable). By creating and assigning a local variable to which the RPC return status is subsequently assigned, you can detect circumstances in which a NULL return status is returned:

DECLARE @ret INT = 123
EXEC @ret = [MyLinkedServer].RemoteDb.dbo.usp_RemoteStoredProcedure
 
IF @ret = NULL
  RAISERROR('Query timeout!', 11, 1)

Note that while the NULL return status is a symptom of a RPC timeout, it remains possible that other remote errors might behave in the same way. If you find that you're detecting 'timeouts' that don't look like timeouts (e.g. if not enough time has elapsed for it to be a real timeout), you may want to think again…