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


Run a query against registered servers and insert results into table


Run a query against registered servers and insert results into table

Author
Message
melanie.gaff
melanie.gaff
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 26
I am trying to do the exact same thing. I don't see that any of these suggestions will result in the solution you are after. If you come up with something, I would be interested in knowing the solution. I'm currently stuck with running the queries through the Registered Servers option and then saving the results and importing them to tables in a database. Very awkward, but feasible.
Dan.Humphries
Dan.Humphries
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2641 Visits: 1212
Just a thought but if you have to do more work to avoid creating an SSIS as a "on off" then it is still worth creating the SSIS even if you have no intention of ever running it again.

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
Guillaume Hermans-172197
Guillaume Hermans-172197
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 133
I wonder if you found a solution. I'm also very interested.
mhorner 67968
mhorner 67968
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 51
The only way I see to do this is to create a linked server and write the individual results into the table using a linked server.
Staggerlee
Staggerlee
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1228 Visits: 1173
Hi,

So many years later, im not sure what i did back then, but now i just use PowerShell. load SQLPS module connect to the CMS Server query the msdb to get the list of servers and then loop through saving the results to a table or excel or what ever i need.

I can supply a example script if anyone wants something to start with.
mhorner 67968
mhorner 67968
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 51
I'm using PowerShell as well but not with the CMS. How does that work? I'd be appreciative of that information and a copy of your script if you don't mind. Thanks, Marcus.
Lowell
Lowell
SSC Guru
SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)

Group: General Forum Members
Points: 75878 Visits: 40999
mhorner 67968 (11/12/2015)
I'm using PowerShell as well but not with the CMS. How does that work? I'd be appreciative of that information and a copy of your script if you don't mind. Thanks, Marcus.


you can query the msdb database, of the server that serves up the Central Management Server list.
with that list, you can use powershell to connect to each one;

select * from msdb.dbo.sysmanagement_shared_server_groups_internal
select * from msdb.dbo.sysmanagement_shared_registered_servers_internal

select * from msdb.dbo.sysmanagement_shared_registered_servers
select * from msdb.dbo.sysmanagement_shared_server_groups

here's an example where i pull stuff together:

SELECT ROW_NUMBER()
OVER (
PARTITION BY TheServer.NAME
ORDER BY TheServer.NAME) AS rw,
TheGroup.NAME AS [servergroup],
TheGroup.[Description] AS [groupdescription],
TheServer.NAME,
TheServer.server_name AS [servername],
TheServer.[description] AS [description]
FROM [HOL-WKS-444].msdb.dbo.sysmanagement_shared_server_groups_internal TheGroup
LEFT JOIN [HOL-WKS-444].msdb.dbo.sysmanagement_shared_registered_servers_internal TheServer
ON TheGroup.server_group_id = TheServer.server_group_id
WHERE TheGroup.server_type = 0 --only the Database Engine Server Group
AND server_name IS NOT NULL



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!
mhorner 67968
mhorner 67968
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 51
Thank you very much. Works well.
lello bello
lello bello
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 150
Hello,

Can you send my your example script (Powershell) per private message?

Thx!

Lello
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