检查链接的SQL Server是否正在运行

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

我正在编写一个存储过程,它使用4点表示法在几个不同的链接服务器上运行SELECT查询。

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.

问题是,如果其中一个链接服务器未运行,则查询将失败,并显示错误121('信号量超时期限已过期')。然后其他SELECT查询不会运行,因为此错误会停止执行其余查询。

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

我想检查@@ ERROR然后继续运行其他查询。

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

如果与其中一个链接服务器的连接失败,如何继续运行查询?

I am using SQL 2012.

我正在使用SQL 2012。

1 个解决方案

#1

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

您是否尝试使用TRY-CATCH异常块包围您的单个呼叫?

     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.

sp_testlinkedserver将在执行代码之前在try块内引发异常,但它不会停止执行存储过程。

#1