i put together this prototype;
basically, for every SQL server in msdb.[dbo].[sysmanagement_shared_registered_servers_internal], i'm creating a linked server for it, and passing commands;
my example is just running a fancy @@version++ inventory query.
the try-catch is a definite plus, because some servers might be offline, deprecated, or inaccessible, and could stop the query without it.
--##################################################################################################
--Get the list of SQL servers from Central management Server, and make sure we have a linked server by the same name.
--##################################################################################################
-- select * from msdb.[dbo].[sysmanagement_shared_server_groups_internal]
-- select * from msdb.[dbo].[sysmanagement_shared_registered_servers_internal]
DECLARE
@isql VARCHAR(max),
@svrname NVARCHAR(128),
@svr NVARCHAR(128)
declare c1 cursor LOCAL FORWARD_ONLY STATIC READ_ONLY for
--###############################################################################################
--cursor definition
--###############################################################################################
WITH KnownClusteredNodes(name)
AS
(
SELECT 'XXX-SQL-P200' UNION ALL
SELECT 'XXX-SQL-P201' UNION ALL
SELECT 'XXX-SQL-P01' UNION ALL
SELECT 'XXX-SQL-P02' UNION ALL
SELECT 'XXX-SQL-C01' UNION ALL
SELECT 'XXX-SQL-C02' UNION ALL
SELECT 'XXX-BI0-P01' UNION ALL
SELECT 'XXX-BI0-P02'
)
select
name,
server_name
from msdb.[dbo].[sysmanagement_shared_registered_servers_internal]
WHERE server_type = 0 --SQL server
AND name not in (SELECT name FROM KnownClusteredNodes)
--###############################################################################################
open c1
fetch next from c1 into @svrname,@svr
While @@fetch_status <> -1
begin
IF NOT EXISTS(SELECT * FROM master.sys.servers WHERE name = @svrname)
BEGIN
PRINT 'Adding Linked Server ' + quotename(@svrname)
--EXEC master.dbo.sp_addlinkedserver @server = @svrname, @srvproduct=N'SQL Server'
--EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=@svrname,@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
END
fetch next from c1 into @svrname,@svr
end
close c1
deallocate c1
--##################################################################################################
--query the servers for :
--##################################################################################################
--bad or invalid Central Management Servers
DECLARE
@MyCommand AS VARCHAR(max),
@val AS VARCHAR(128),
@ErrorNumber AS INT,
@ErrorSeverity AS INT,
@ErrorState AS INT,
@ErrorLine AS INT,
@ErrorProcedure AS NVARCHAR(126),
@ErrorMessage AS NVARCHAR(4000),
@ErrorString AS NVARCHAR(4000)
declare linkedCursor cursor LOCAL FORWARD_ONLY STATIC READ_ONLY for
--###############################################################################################
--cursor definition
--###############################################################################################
select name from master.sys.servers where product = 'SQL Server' AND provider ='SQLNCLI' and server_id > 0
--###############################################################################################
open linkedCursor
fetch next from linkedCursor into @val
While @@fetch_status <> -1
BEGIN
BEGIN TRY
SELECT @MyCommand = 'SELECT * FROM OPENQUERY(' + quotename(@val) + ',''Select @@version As Version,
Serverproperty(''''BuildClrVersion'''') AS BuildClrVersion,
Serverproperty(''''ComputerNamePhysicalNetBIOS'''') AS ComputerNamePhysicalNetBIOS,
Serverproperty(''''Edition'''') AS Edition,
CASE Parsename(CONVERT(VARCHAR(30), Serverproperty(''''productversion'''')), 4)
+ ''''.''''
+ Parsename(CONVERT(VARCHAR(30), Serverproperty(''''productversion'''')), 3)
WHEN ''''9.00'''' THEN ''''SQL 2005''''
WHEN ''''10.0'''' THEN ''''SQL 2008''''
WHEN ''''10.50'''' THEN ''''SQL 2008R2''''
WHEN ''''11.0'''' THEN ''''SQL 2012''''
WHEN ''''12.0'''' THEN ''''SQL 2014''''
WHEN ''''13.0'''' THEN ''''SQL 2016''''
ELSE Parsename(CONVERT(VARCHAR(30), Serverproperty(''''productversion'''')), 4)
+ ''''.''''
+ Parsename(CONVERT(VARCHAR(30), Serverproperty(''''productversion'''')), 3)
END As SQLVersion,
Serverproperty(''''productversion'''') As ProductVersion,
Serverproperty(''''EditionID'''') AS EditionID,
Serverproperty(''''EngineEdition'''') AS EngineEdition,
Serverproperty(''''MachineName'''') AS MachineName,
Serverproperty(''''ProductLevel'''') AS ProductLevel,
Serverproperty(''''ResourceLastUpdateDateTime'''') AS ResourceLastUpdateDateTime,
Serverproperty(''''ResourceVersion'''') AS ResourceVersion,
Serverproperty(''''ServerName'''') AS ServerName,
Serverproperty(''''InstanceName'''') AS InstanceName '')'
EXECUTE (@MyCommand)
END TRY
BEGIN CATCH
SELECT
@ErrorNumber = Error_number(),
@ErrorSeverity = Error_severity(),
@ErrorState = Error_state(),
@ErrorLine = Error_line(),
@ErrorProcedure = Error_procedure(),
@ErrorMessage = Error_message();
SELECT
@ErrorString = '*** Error Num: '
+ CONVERT (VARCHAR(5), CONVERT(INT, @ErrorNumber, 0))
+ ' Severity: '
+ CONVERT (VARCHAR(5), CONVERT(INT, @ErrorSeverity, 0))
+ ' State: '
+ CONVERT (VARCHAR(5), CONVERT(INT, @ErrorState, 0))
+ ' Line: '
+ CONVERT (VARCHAR(5), CONVERT(INT, @ErrorLine, 0))
+ ' Procedure: '
+ Isnull(@ErrorProcedure, '') + ' Message: '
+ Isnull(@ErrorMessage, '');
PRINT @ErrorString
END CATCH
fetch next from linkedCursor into @val
end
close linkedCursor
deallocate linkedCursor
--failed jobs
--offline databases.
--##################################################################################################
Lowell