如何在 TSQL 中测试linkedserver 的连接性 [英] How to test linkedserver's connectivity in TSQL

问题描述

我需要编写一个程序来从多个远程服务器收集数据，

I need to write a procedure to collect data from several remote servers,

我使用链接服务器和 OPENQUERY 从服务器收集数据，但有时我会失去与某些服务器的连接，或者我根本无法连接它们(例如远程服务器脱机)- 在这些情况下，OPENQUERY 会导致超时.

I use linkedservers and OPENQUERY to gather data from servers, but sometimes I lose connection to some servers or I simply can't connect them (e.g. remote server is offline)- and OPENQUERY results in time-outs in these cases.

所以我想先检查linkedservers的连接性，然后如果它成功运行查询，如果不是就转到下一个远程服务器.

So I wanted to check linkedservers connectivity first and then if it's successful run the query, if not just move on to next the remote server.

我尝试将 OPENQUERY 放入 TRY - CATCH 但它仍然返回超时错误，sp_testlinkedserver 过程也返回超时错误.

I tried to put OPENQUERY in TRY - CATCH but it still returned me time-out error, sp_testlinkedserver procedure also returned me time-out error.

非常感谢任何帮助.

推荐答案

你可以使用下面的脚本

https://web.archive.org/web/20190201090243/https://blogs.msdn.microsoft.com/sqltips/2005/06/07/test-linked-server-连接设置/

declare @srvr nvarchar(128), @retval int;
set @srvr = 'my_linked_srvr';
begin try
    exec @retval = sys.sp_testlinkedserver @srvr;
end try
begin catch
    set @retval = sign(@@error);
end catch;
if @retval <> 0
  raiserror('Unable to connect to server. This operation will be tried later!', 16, 2 );

这篇关于如何在 TSQL 中测试linkedserver 的连接性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！