Check if a linked SQL server is running
sql-server, sql-server-2012
Solution
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.
Problem
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.