Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Run/create a query/datasource to Central Management Server query in SSRS Expand / Collapse
Author
Message
Posted Friday, January 15, 2010 7:43 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, June 24, 2016 12:07 PM
Points: 2,777, Visits: 4,087
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
Post #848276
Posted Monday, February 7, 2011 2:03 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, March 29, 2016 10:11 AM
Points: 1,213, Visits: 2,160
Bumping this thread as this is something I'm looking to implement as well.

---
SQLSlayer
Making SQL do what we want it to do.

Post #1059905
Posted Tuesday, February 21, 2012 1:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 27, 2015 1:54 PM
Points: 7, 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.
Post #1255555
Posted Tuesday, September 4, 2012 5:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 20, 2016 2:36 AM
Points: 3, Visits: 176
Anyone find an answer for this one?
Post #1353817
Posted Tuesday, September 4, 2012 10:48 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, March 29, 2016 10:11 AM
Points: 1,213, Visits: 2,160
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.

Post #1354048
Posted Tuesday, May 3, 2016 11:55 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 6:33 PM
Points: 72, Visits: 376
Hi,
Can i see your stored procedure? I want to do the same thing to create SSRS report for 30+ different servers.
Post #1783046
Posted Tuesday, May 3, 2016 12:20 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:38 AM
Points: 14,413, Visits: 37,714
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!
Post #1783063
Posted Tuesday, May 3, 2016 12:28 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 6:33 PM
Points: 72, Visits: 376
Thanks. I think i can modify this stored procedure to get any information like database information,job information, backup information etc right?
Post #1783067
Posted Tuesday, May 3, 2016 12:42 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, June 24, 2016 12:07 PM
Points: 2,777, Visits: 4,087
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
Post #1783075
Posted Tuesday, May 3, 2016 1:01 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 6:33 PM
Points: 72, Visits: 376
I have never done any powershell script so do not know how but i will search and see how i can do it.
Post #1783085
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse