To Test the connectivity of Linked Server and See list of linked server in SQL

To Test the connectivity of Linked Server and See the list of linked Server in SQL:

Method 1:





Scripts:

EXEC sp_addlinkedserver   'SEATTLESales','SQL Server' ---------To add the linked server list

select * from sys.servers---------To see the linked server list

exec sp_linkedservers---------To see the linked server list

1)exec sp_testlinkedserver linkserverame----------To test the linked server connectivity


2)sp_tables_ex linkservername----------To test the linked server connectivity


3)----------To test the linked server connectivity using script-------------
declare @srvr nvarchar(128), @retval int;
set @srvr = 'linkedservername';
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 );

Method 2:

How to test linked server connectivity?

Execute the following Microsoft SQL Server Transact-SQL script to create a stored procedure to perform a ping test for the connectivity of a linked server:

USE AdventureWorks2008;

GO

CREATE PROC procPingLinkedServer  @LinkedServer sysname

/*  RETURNS

        1 --> Connection success

        0 --> Connection failure

*/

AS

BEGIN

DECLARE @Command nvarchar(1048)

SET NOCOUNT ON;

CREATE TABLE #PingTest (  CmdResultBuffer varchar(128));

SET @Command = 'ping '+ @LinkedServer

PRINT @Command

INSERT #PingTest

    EXEC master..xp_cmdshell   @Command;

IF EXISTS ( SELECT 1 FROM #PingTest WHERE CmdResultBuffer LIKE '%TTL%' )

    RETURN 1;

ELSE

    RETURN 0;

drop table #PingTest

END

GO

-- Test linked server connectivity

DECLARE @Connection int

EXEC @Connection = procPingLinkServer 'LINKEDSERVERALPHA'

SELECT ConnectionStatus = @Connection