Check if a linked SQL server is running

Question

I am writing a stored procedure which runs SELECT queries on several different linked servers using the 4-dot notation.

The problem is, if one of the linked servers is not running, the query fails with error 121 ('The semaphore timeout period has expired'). The other SELECT queries then don't run as this error stops the rest of the query executing.

I wanted to check @@ERROR then continue running the other queries.

How can I continue running the query if the connection to one of the linked servers fails?

I am using SQL 2012.

Answer 1

Have you tried to surround your single call with TRY-CATCH exception blocks?

     BEGIN TRY
          --First Server Connection (Server1) 192.168.1.x
          --If the connection isn't available it will raise an exception
          exec sp_testlinkedserver  @servername = Server1
          --SQL statement here
     END TRY
     BEGIN CATCH
          SELECT ERROR_MESSAGE()
     END CATCH

     BEGIN TRY
          --Second Server Connection (Server2) 192.168.2.x
          --If the connection isn't available it will raise an exception
          exec sp_testlinkedserver  @servername = Server2
          --SQL statement here
     END TRY
     BEGIN CATCH
          SELECT ERROR_MESSAGE()
     END CATCH 

sp_testlinkedserver will raise an exception inside a try block before the execution of your code but it won't stop the execution of the stored procedure.