如何在TSQL中测试linkedserver的连接 [英] How to test linkedserver's connectivity in TSQL
问题描述
我需要编写一个过程来从多个远程服务器收集数据,
I need to write a procedure to collect data from several remote servers,
我使用linkedservers和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.
我真的很感激任何帮助。
I'd really appreciate any help.
推荐答案
您可以使用以下脚本
http://blogs.msdn.com/b/sqltips/archive/2005/06/07/426578.aspx
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屋!