Import data from about 50 different servers to a local database ta

Hello all,
First of all thank you very much for all your help.
Can you please tell me what is the best way to import data (select
@.@.version, exec xp_fixeddrives, sp_helpdb and other) from about 50 different
servers to a local database table. I would be great if job itself would ente
r
location name into
the table so it will look like data came from different location and not
from one.
I would think first I will need to create Remote servers under local SQL
server
database so my job will recognize different locations. And then ...
Thanks,
-DHi
If you have all the servers as linked servers you would need to use dynamic
SQL to change the name of the linked server in your code (without repeating
the code 50 times!). If your code inserted the output into a staging table,
then you could use the variable holding the server name whilst inserting the
data into the destination table.
e.g.
-- Destination table
CREATE TABLE ALLServerDrives ( servername sysname, drive char(1), size
bigint )
DECLARE @.linkedServer sysname
SET @.linkedServer = 'RemoteServer'
CREATE TABLE #drives ( drive char(1), size bigint )
EXEC ('INSERT INTO #drives ( drive, size )
EXEC ' + @.linkedServer + '.master.dbo.xp_fixeddrives' )
INSERT INTO ALLServerDrives ( servername, drive, size )
select @.linkedServer, Drive, size FROM #drives
drop table #drives
SELECT * FROM ALLServerDrives
John
"D''Animal" wrote:

> Hello all,
> First of all thank you very much for all your help.
> Can you please tell me what is the best way to import data (select
> @.@.version, exec xp_fixeddrives, sp_helpdb and other) from about 50 differe
nt
> servers to a local database table. I would be great if job itself would en
ter
> location name into
> the table so it will look like data came from different location and not
> from one.
> I would think first I will need to create Remote servers under local SQL
> server
> database so my job will recognize different locations. And then ...
> Thanks,
> -D