Monday, January 25, 2010

Check all linked servers connectivity status

Applied to SQL Server 2005 or greater.
I often work with a lot of linked servers (over 100), so I created this script to check their connectivity.

DECLARE @SRV_NAME NVARCHAR(50);
DECLARE @Msg NVARCHAR(4000);
DECLARE @retval INT;
DECLARE @LinkedServers
TABLE(SRV_NAME NVARCHAR(50)
, SRV_PROVIDERNAME VARCHAR(50)
, SRV_PRODUCT VARCHAR(50)
, SRV_DATASOURCE VARCHAR(50)
, SRV_PROVIDERSTRING VARCHAR(50)
, SRV_LOCATION VARCHAR(50)
, SRV_CAT VARCHAR(50));
DECLARE @ErrorLog
TABLE(LogDate SMALLDATETIME
, SRV_NAME NVARCHAR(50)
, ErrorMsg NVARCHAR(4000));
INSERT INTO @LinkedServers
EXEC sp_linkedservers
DECLARE Cur CURSOR FOR
SELECT SRV_NAME FROM @LinkedServers
OPEN Cur
FETCH FROM Cur
INTO @SRV_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
PRINT @SRV_NAME;
EXEC @retval = sys.sp_testlinkedserver @SRV_NAME;
END TRY
BEGIN CATCH
SET @Msg = ERROR_MESSAGE();
INSERT INTO @ErrorLog(LogDate, SRV_NAME, ErrorMsg)
VALUES(GETDATE(), @SRV_NAME, @Msg);
END CATCH
FETCH FROM Cur
INTO @SRV_NAME
END
CLOSE Cur
DEALLOCATE Cur
SELECT * FROM @ErrorLog;

No comments:

Post a Comment