Steve Jones commented on a blog post I did on Checking the SQL Server Agent Status on all SQL Servers and pointed out that my query didn’t return the Server Name with the resultset. My first reaction/thought was “hmmm did my code not cut/paste correctly?” then it dawned on me that my default settings might not be the same default settings that everyone else uses.
I have in my Multiserver results options that the query returns the server name to the result set. If this is a setting you’re not familar with follow me on this to find where to change these options if desired. In (SQL Server 2008) Management Studio click on Tools then Options.
In the options window Expand Query Results, then SQL Server, then select MultiServer Results… your window should look something like this now:
Real easy to find eh? LOL
Let’s discuss the 3 options in here:
- Add Login name to the results: This will add a column to the result set with the login name of the account that executed the CMS query
- Add ServerName to the results: This will add a column to the result set indicating the instance name that the query was ran against
- Merge Results: Setting this value to true will take all the results from your CMS query and consolidate the data into one result set as opposed to a separate resultset per instance
So play around with these settings and see what works best for you