SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Run/create a query/datasource to Central Management Server query in SSRS


Run/create a query/datasource to Central Management Server query in SSRS

Author
Message
Jason Selburg
Jason Selburg
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6990 Visits: 4115
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 you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
Adam Bean
Adam Bean
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6710 Visits: 2222
Bumping this thread as this is something I'm looking to implement as well.

---
SQLSlayer
Making SQL do what we want it to do.
Eric Higgins
Eric Higgins
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 Visits: 91
(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.
richard_murphy
richard_murphy
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 180
Anyone find an answer for this one?
Adam Bean
Adam Bean
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6710 Visits: 2222
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.

---
SQLSlayer
Making SQL do what we want it to do.
SqlServerLover
SqlServerLover
SSC Veteran
SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)

Group: General Forum Members
Points: 226 Visits: 417
Hi,
Can i see your stored procedure? I want to do the same thing to create SSRS report for 30+ different servers.
Lowell
Lowell
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73225 Visits: 40960
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
SqlServerLover
SqlServerLover
SSC Veteran
SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)

Group: General Forum Members
Points: 226 Visits: 417
Thanks. I think i can modify this stored procedure to get any information like database information,job information, backup information etc right?
Jason Selburg
Jason Selburg
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6990 Visits: 4115
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 you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
SqlServerLover
SqlServerLover
SSC Veteran
SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)

Group: General Forum Members
Points: 226 Visits: 417
I have never done any powershell script so do not know how but i will search and see how i can do it.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search