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

Related article:

How to Achieve High Performance with Linked Servers