SQL SERVER
bedbmsguru
2018. 10. 26. 21:50
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