As we are preparing to migrate some of our older SQL Server databases that are nearing their end of support to SQL Server 2019, one of the necessary tasks is determining what Linked Servers need to be established on the new servers to maintain the existing functionality for all of our users’ applications.
Testing the functionality of an individual Linked Server is simple enough – the built-in stored procedure sp_testlinkedserver will return a zero (0) if the link is good, and returns an exception if the link is not.
There is a stored procedure for listing all Linked Servers, but it doesn’t return the health of the link: sp_linkedservers.
How can these be combined to return only the Linked Servers that are currently up?
The solution I developed uses sp_linkedservers to populate a table variable, which is then used in a cursor. For each row in the table, using the sp_testlinkedserver stored proc, an active column is set to 1 if the server is up, and 0 if it is not.
DECLARE @LinkedServerName NVARCHAR(128), @Active BIT, @Message NVARCHAR(MAX)=''
DECLARE @Servers TABLE
(SRV_NAME NVARCHAR(128)
,SRV_PROVIDERNAME NVARCHAR(128)
,SRV_PRODUCT NVARCHAR(128)
,SRV_DATASOURCE NVARCHAR(4000)
,SRV_PROVIDERSTRING NVARCHAR(4000)
,SRV_LOCATION NVARCHAR(4000)
,SRV_CAT NVARCHAR(128)
,Active BIT DEFAULT 1)
INSERT INTO @Servers (SRV_NAME,SRV_PROVIDERNAME,SRV_PRODUCT,SRV_DATASOURCE,SRV_PROVIDERSTRING,SRV_LOCATION,SRV_CAT)
EXEC sp_linkedservers
DECLARE servercursor CURSOR FOR
SELECT SRV_NAME, Active
FROM @Servers
ORDER BY SRV_NAME
OPEN servercursor
FETCH NEXT FROM servercursor INTO @LinkedServerName, @Active
WHILE @@FETCH_STATUS=0
BEGIN
DECLARE @isUp BIT = 0
BEGIN TRY
DECLARE @test INT
EXEC @test = sp_testlinkedserver @LinkedServerName;
IF @test = 0
BEGIN
SET @isUp = 1
END
ELSE
BEGIN
SET @isUp = 0
END
END TRY
BEGIN CATCH
SET @isUp = 0;
END CATCH
IF @Active<>@isUp
BEGIN
UPDATE @Servers
SET Active=@isUp
WHERE SRV_NAME=@LinkedServerName
SET @Message = @Message + 'Active bit on server ' + @LinkedServerName + ' was set to ' + CONVERT(VARCHAR,@isUp) + '.' + CHAR(10) + CHAR(13)
END
FETCH NEXT FROM servercursor INTO @LinkedServerName, @Active
END
CLOSE servercursor
DEALLOCATE servercursor
SELECT SRV_NAME
FROM @Servers
WHERE Active=1
ORDER BY SRV_NAME
PRINT @Message
Running this code will return an alphabetically ordered list of active linked servers.