Test Your Linked Server Connections

DECLARE @Cursor CURSOR
DECLARE @ServerName NVARCHAR(128)
DECLARE @ServerID INT
DECLARE @SQL VARCHAR(MAX)

--Create temp table to store results
IF object_id(N'tempdb..##LinkedServers') IS NOT NULL
DROP TABLE ##LinkedServers

CREATE TABLE ##LinkedServers
(
[LinkedServerID] INT IDENTITY(1,1) NOT NULL,
[Name] SYSNAME NULL,
[ProvName] NVARCHAR(128) NULL,
[Product] NVARCHAR(128) NULL,
[DataSource] NVARCHAR(4000) NULL,
[ProvString] NVARCHAR(4000) NULL,
[Location] NVARCHAR(4000) NULL,
[Cat] SYSNAME NULL
)

--Get list of linked servers from system proc
INSERT INTO ##LinkedServers
EXEC [sys].sp_linkedservers

--Add tested field to result set
ALTER TABLE ##LinkedServers ADD [TestSuccess] BIT

--Cursor over list of linked servers testing each
SET @Cursor = CURSOR FOR
SELECT
[LinkedServerID],
[Name]
FROM
##LinkedServers

OPEN @Cursor
FETCH NEXT FROM @Cursor INTO
@ServerID,
@ServerName

WHILE (@@FETCH_STATUS = 0)
BEGIN

SET @SQL = 

'
BEGIN TRY

EXEC sp_testlinkedserver [' + @ServerName + ']

UPDATE
##LinkedServers
SET
[TestSuccess] = 1
WHERE
[LinkedServerID] = ' + CAST(@ServerID AS VARCHAR) + '

END TRY
BEGIN CATCH

UPDATE
##LinkedServers
SET
[TestSuccess] = 0
WHERE
[LinkedServerID] = ' + CAST(@ServerID AS VARCHAR) + '

END CATCH
'

EXEC(@SQL)

FETCH NEXT FROM @Cursor INTO
@ServerID,
@ServerName

END

--Return results
SELECT 
[Name] AS 'LinkedServerName',
[Product],
[TestSuccess] 
FROM 
##LinkedServers

DROP TABLE ##LinkedServers