How to test the connection to a Linked Server?

You may want to test the connection to a Linked Server
before executing a query/sp against the Linked Server.

In SQL Server 2000 there is no way to test the connection
without execution a query/sp against the Linked Server.
In SQL Server 2005 you can use system stored procedure
named "sp_testlinkedserver".It accepts Linked Server name
and returns 0 in case of success and 1 in case of failure.

DECLARE @LinkedServerName SYSNAME,@RC INT
SET @LinkedServerName='TargetLinkedServer'
BEGIN TRY
EXEC @RC=sys.sp_testlinkedserver @LinkedServerName
END TRY
BEGIN CATCH
SET @RC=SIGN(@@ERROR)
END CATCH
IF @RC<>0
RAISERROR('There was en error connecting to "%s" server.',16,1,@LinkedServerName)
ELSE
PRINT 'You should be able to connect to "'+@LinkedServerName+'" server.'

For more information:
http://technet.microsoft.com/en-us/library/ms189809.aspx

Tweet