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 );
Wednesday, June 20, 2007
Subscribe to:
Post Comments (Atom)
1 comment:
Post a Comment