I am trying to execute this stored procedure xp_fixeddrived on one
server, get the server name and store the data on another server. I
have tried using a data flow task or an execute SQL task.

This code runs on Server A:


CREATE TABLE [dbo].[#tblDriveSpaceTemp](
[Drive] [varchar](4) NULL,
[FreeSpace] int NULL
) ON [PRIMARY]

INSERT INTO #tblDriveSpaceTemp exec master.dbo.xp_fixeddrives

SELECT @@ServerName AS ServerName, Drive, [FreeSpace]
FROM #tblDriveSpaceTemp

And returns this result set:

ServerName Drive FreeSpace
MAPDB56\PROD C 8778
MAPDB56\PROD D 2020
MAPDB56\PROD F 132321
MAPDB56\PROD L 41994
MAPDB56\PROD Q 488
MAPDB56\PROD R 3567
MAPDB56\PROD S 307120
MAPDB56\PROD U 485

The result set needs to be saved to a table on Server B.

Stephen Anderson


MS Sql Server LazyDBA home page