Linked Server Connectivity using T-SQL

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