0

Currently, I have a script queries my CMS server to get the list of servers in a certain CMS group. With that list, I perform a foreach loop to execute a query against each instance in the CMS group individually.

I am wondering if there is a way (such as in SSMS) where my query can be run against all targets in the CMS group and results from all targets are shown in one table?

Essentially I don't want to end up with my results separated. I would like them all in one table. If this is possible it would be easier than formatting data rows in PowerShell.

Improve this question
5
  • 1
    Have you considered having your query just join the results together in a temp table in your loop? You can have a temp table dynamically generate itself from the generated list for the number of rows needed and you can have each one fill it. Then you can do whatever you want with the result set such as export to CSV or even keep it in a auto generated table.
    – Ali Razeghi - AWS
    Commented Aug 23, 2016 at 19:26
  • I have not, this is the first time I have heard of that? Could you provide an example if possible? Going to research that though right now. Thank you.
    – choloboy
    Commented Aug 23, 2016 at 19:28
  • Heya I just thought of a way easier way if you're using powershell. You can just keep appending the output to a file. Would that work for you?
    – Ali Razeghi - AWS
    Commented Aug 24, 2016 at 1:27
  • im trying to write a table to an event log. Not sure if its possible, or just a list of two columns (server name and db name). Will give that a try. Thank you.
    – choloboy
    Commented Aug 24, 2016 at 13:25
  • Noted. is that the final desired location, is the event log? If so I'm sure Powershell could make that happen. Try a solution and post so we can review if it doesn't work.
    – Ali Razeghi - AWS
    Commented Aug 24, 2016 at 19:07

1 Answer 1

Reset to default
1

Since you are using Powershell anyways, I'm not sure why you wouldn't just want to do the loop and return the results from each server to a data object.

If you want to break out of powershell and do it from SSMS, as long as you know your servers to begin with you can look into using Registered Server groups which allow you to run a query against multiple SQL instances at once.

Basically, from SSMS, go to View > Registered Servers. It should appear in it's default location on the left. From there, drill down to Local Server Groups, right click > New Server Group.
Add the group name 'CMS Group'.
Then right click on the new group and select 'New Server Registration'. Fill in your connection details and add the server.
Repeat that process until all your servers are added to the group.

Once the group is complete you can right click on the group folder and choose 'New Query'.
A new query window will open with a pinkish red color at the bottom where you would normally see your connection details. This new query window will run against all members of that group.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.