adminbd

Записи по mssql,oracle,windows и linux

Have a Question?

If you have any question you can ask below or enter what you are looking for!

Проверка доступности линкованных серверов

 

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

 

Leave a Reply

Your email address will not be published. Required fields are marked *

You can use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>