April 7, 2026 at 7:40 pm
I am annoyed. We have a CMS server that has hundreds of servers saved to the "node." I need to query them all at once to get a COUNT of a certain value. I want a total across all the servers, not the total on each server. But because CMS keeps throwing Server Name as the first column in every query I write, I'm getting individual server counts, not the end total.
SELECT ... INTO doesn't work because if I use a local temp table, the totals won't carry over to the other servers. If I use a global temp table, it already exists.
Likewise, creating the table in advance. The kicker is no matter what I do, that Server Name column keeps popping up and preventing me from getting a SUM() or a COUNT() over the total.
For Reasons, I cannot export the data or save it into a database. Does anyone have another solution?
April 8, 2026 at 8:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
April 8, 2026 at 9:06 pm
Hi Brandie, will writing a subquery work for this issue? Such as the below example?
select sum(count)
from
( select servername, count = count(*)
from CMSservers
group by servername
) a1
Tung Dang
Azure and SQL Server DBA Contractor / Consultant
SQL Brainbox - SQL Server Monitoring Tool
April 9, 2026 at 12:45 pm
It will not. I have tried subquerys and the server name still gets added to the top SELECT list, ruining my totals.
It appears I will have to query from another source (like PowerShell) instead of an SSMS window. I will try that.
April 9, 2026 at 4:59 pm
It sounds like you are running the query across multiple SERVERS, not databases or tables, then you really only have 3 options:
1- use linked servers
2- export to file instead of screen and parse it in an external tool.
3- build a report (power BI, ssrs, etc)
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 12, 2026 at 1:48 am
There's a way, it's just not pretty.
First, in SSMS, go to Tools->Options->Query Results->SQL Server->MultiServer Results, and set "Add server name to the results" to False.
Second, write the query that will run on each server to output results as a query you can run elsewhere. (example below)
Switch the query output to Text (Ctrl+T) in the multiserver window before running the query.
Example: I created a Sever Group by adding three connections to two instances. Then I opened a new query on the Serve Group and hit [Ctrl]+T to get the results as text. Then I ran the following:
SET NOCOUNT ON; -- this is important
-- gather some data to report
DECLARE @ObjCount int = (SELECT COUNT(*) FROM sys.objects),
@DBCount int = (SELECT COUNT(*) FROM sys.databases),
@ProductMajorVersion varchar(1024) = CONVERT(varchar(1024), SERVERPROPERTY('ProductMajorVersion'));
-- produce consumable output
SELECT 'SELECT ' + CONVERT(VARCHAR(1024), @ObjCount) + ' AS [ObjCount], '
+ CONVERT(VARCHAR(1024), @DBCount) + ' AS [DBCount], '
+ @ProductMajorVersion + ' AS [ProductMajorVersion] UNION ALL';
That produced the following as a single block of text:
SELECT 133 AS [ObjCount], 10 AS [DBCount], 16 AS [ProductMajorVersion] UNION ALL
SELECT 133 AS [ObjCount], 10 AS [DBCount], 16 AS [ProductMajorVersion] UNION ALL
SELECT 116 AS [ObjCount], 10 AS [DBCount], 15 AS [ProductMajorVersion] UNION ALL
SELECT 133 AS [ObjCount], 10 AS [DBCount], 16 AS [ProductMajorVersion] UNION ALL
SELECT 116 AS [ObjCount], 10 AS [DBCount], 15 AS [ProductMajorVersion] UNION ALL
SELECT 116 AS [ObjCount], 10 AS [DBCount], 15 AS [ProductMajorVersion] UNION ALL
Then I opened a new single-server query on a local instance, and wrote a query to consume the text output:
SELECT SUM([ObjCount]) AS [ObjCount], SUM([DBCount]) AS [DBCount],
MIN([ProductMajorVersion]) AS [MinProductMajorVersion]
FROM (
-- begin pasted text
-- pasted text goes here
-- end pasted text
) a
Then I pasted in the text from the multiserver query, deleted the final UNION ALL, and ran it:
SELECT SUM([ObjCount]) AS [ObjCount], SUM([DBCount]) AS [DBCount],
MIN([ProductMajorVersion]) AS [MinProductMajorVersion]
FROM (
-- begin pasted text
SELECT 133 AS [ObjCount], 10 AS [DBCount], 16 AS [ProductMajorVersion] UNION ALL
SELECT 133 AS [ObjCount], 10 AS [DBCount], 16 AS [ProductMajorVersion] UNION ALL
SELECT 116 AS [ObjCount], 10 AS [DBCount], 15 AS [ProductMajorVersion] UNION ALL
SELECT 133 AS [ObjCount], 10 AS [DBCount], 16 AS [ProductMajorVersion] UNION ALL
SELECT 116 AS [ObjCount], 10 AS [DBCount], 15 AS [ProductMajorVersion] UNION ALL
SELECT 116 AS [ObjCount], 10 AS [DBCount], 15 AS [ProductMajorVersion]
-- end pasted text
) a
Results:
ObjCount DBCount MinProductMajorVersion
----------- ----------- ----------------------
747 60 15
(1 row affected)
Eddie Wuerch
MCM: SQL
April 22, 2026 at 4:47 pm
GO go | IT'S Ok
CREATE a TSQL script used to create/generate a TSQL thus:
SELECT OPENDATASOURCE
UNION ALL
SELECT OPENDATASOURCE
UNION ALL
SELECT OPENDATASOURCE
ETC?
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply