January 15, 2010 at 7:43 am
Simply put, you can run a query against all servers in the central management server group using SSMS. Does anyone know how to create a reporting services datasource that would mimic this action?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgFebruary 7, 2011 at 2:03 pm
Bumping this thread as this is something I'm looking to implement as well.
February 21, 2012 at 1:37 pm
(bump) ... I would also like to do this if anyone knows how to go about it. I'm considering doing my CMS work and pointing SSRS to the resultant data, but I'd rather touch the CMS directly.
September 4, 2012 at 5:10 am
Anyone find an answer for this one?
September 4, 2012 at 10:48 am
While not overly thrilled with the idea, what I ended up doing was simply creating stored procedures with open datasource calls and dynamically calling the server based on a parameter that fed from my central list of inventory.
May 3, 2016 at 11:55 am
Hi,
Can i see your stored procedure? I want to do the same thing to create SSRS report for 30+ different servers.
May 3, 2016 at 12:20 pm
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
May 3, 2016 at 12:28 pm
Thanks. I think i can modify this stored procedure to get any information like database information,job information, backup information etc right?
May 3, 2016 at 12:42 pm
It would behoove you to look into a PowerShell solution instead. Just my 2 cents.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMay 3, 2016 at 1:01 pm
I have never done any powershell script so do not know how but i will search and see how i can do it.
May 3, 2016 at 1:06 pm
Thanks so much Lowell.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply