Skip to content

How to test linkedserver’s connectivity in TSQL

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

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.

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.

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.



You can use below script

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 );
User contributions licensed under: CC BY-SA
6 People found this is helpful