How to join the updated result set of multiple servers

Question

I have got 4 different servers and I want to get the free diskspace of all those in a single output.
using xp_fixeddrives we will get the free space in each drive .

Its not possible to get into all servers and check it as above.So I am trying to execute all it a once.

for example the result set of one server is as follows

drive   free_mb
C       1816
D       2553
E       2882
F       6247
G       16936
H       7281
I       2395

As I am a starter to sql please do help me to sort out this issue .

Answer 1

Something like this should do it:

create table #Tmp
(
  Drive char(1),
  MBFree int,
  ServerName varchar(50)
)

insert into #Tmp(Drive, MBFree) exec Server1.master.dbo.xp_fixeddrives
update #Tmp set ServerName = 'Server1' where ServerName is null

insert into #Tmp(Drive, MBFree) exec Server2.master.dbo.xp_fixeddrives
update #Tmp set ServerName = 'Server2' where ServerName is null

select *
from #Tmp
order by ServerName, Drive

drop table #Tmp

Result:

Drive MBFree      ServerName
----- ----------- --------------------------------------------------
C     57859       Server1
D     384636      Server1
F     82353       Server1
C     4963        Server2
E     10605       Server2

Server1 and Server2 has to be Linked Servers in the server where you are executing the above statements.

Answer 2

SELECT [Server] = 'Server1', Drive, [MB free] 
  FROM OPENQUERY([Server1], 'SET FMTONLY OFF;EXEC xp_fixeddrives')
UNION ALL
SELECT [Server] = 'Server2', Drive, [MB free]  
  FROM OPENQUERY([Server2], 'SET FMTONLY OFF;EXEC xp_fixeddrives');

Answer 3

If all you want to do is inspect the results of a query that runs on multiple servers (as opposed to doing something with the combined results), you can use either Registered Servers, or set up a Central Management Server (CMS). (This is something I recommend doing anyways if you manage more than one server.)

These facilities allow you not only to run queries against groups of servers, but also to use Policy-Based Management to validate and maintain server and database configurations (depending on your environment).

---

If you do need further processing of the combined results, you'll have to use another solution -- the methods in the other answers will certainly work. Depending on what you need to accomplish, you could also leverage things like SSIS, PowerShell, SQLCMD, etc.