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 enter
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
Hi
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 different
> servers to a local database table. I would be great if job itself would enter
> 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