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