Tuesday, November 27, 2012

How to test linkedserver's connectivity using SQL query


declare @Lnksrvr nvarchar(128), @returnvalue int;
set @Lnksrvr = 'LinkedServer_ConnectionName';
begin try
    exec @returnvalue = sys.sp_testlinkedserver @Lnksrvr;
end try
begin catch
    set @returnvalue = sign(@@error);
end catch;
if @returnvalue <> 0
  raiserror('Unable to connect to server. Please try later!', 16, 2 );