Thursday, November 01, 2012

Script to check SQL Server Linked Server Status

 

It leverages on the system stored procedure sys.sp_testlinkedserver


SET NOCOUNT ON;
DECLARE @LinkName NVARCHAR (128), @retval INT, @msg varchar(300)
IF (SELECT COUNT (srvname) FROM sys.sysservers WHERE srvname <> @@SERVERNAME) = 0 RETURN

DECLARE srvname INSENSITIVE CURSOR FOR
        (SELECT srvname FROM sys.sysservers WHERE srvname <> @@SERVERNAME) FOR READ ONLY
OPEN srvname
    FETCH NEXT FROM srvname INTO @LinkName
WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        EXEC @retval = sys.sp_testlinkedserver @LinkName
    END TRY
   
    BEGIN CATCH
        IF @LinkName IS NULL RETURN
        SET @retval = SIGN(@@ERROR)
        IF @retval <> 0
        BEGIN
          SET @msg = 'Unable to connect to the Linked server : ' + @LinkName
          RAISERROR (@msg, 16, 2 )
        END
    END CATCH
    FETCH NEXT FROM srvname INTO @LinkName
   
END   
   
CLOSE srvname
DEALLOCATE srvname

3 comments:

Guide For Andhra Pradesh said...
This comment has been removed by the author.
Guide For Andhra Pradesh said...
This comment has been removed by the author.
Guide For Andhra Pradesh said...

Thanks a lot SIVA. Good Work. Keep Rocking... Was searching on net for this kind of script. Finally got yours. Tested it and its working..