Wednesday, June 20, 2007

Is Linked Server available?

In SQL Server 2000 we have been checking an availability of linked server by using
xp_cmdshell along with 'ping' command.It worked fine but what does SQL Server 2005 propose to us?
There is a system stored procedure (sp_testlinkedserver )that accepts name of linked server as a parameter and returns the status of server.

Take a look at the script below.

DECLARE @srvr NVARCHAR(128), @statusval INT;
SET @srvr = 'Linked_Server';
BEGIN TRY
EXEC @statusval = sp_testlinkedserver @srvr;
END TRY
BEGIN CATCH
SET @statusval = SIGN(@@ERROR);
END CATCH;
IF @statusval <> 0
RAISERROR('Unable to connect to linked server. Try later!', 16, 2 );

1 comment:

aiya said...
This comment has been removed by a blog administrator.