Testing Linked Servers Connections in SQL Server 2005

If you want to test linked server connections in SQL Server 2005, use the code below


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 );