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,
-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 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|||John,
How do I generate unique value (such as date and/or automatically generated
numbers 1,2,3,4...) as new column so it will not overwrite old entries
when it executes?
Thanks
-D
"John Bell" wrote:
> 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|||Hi
As the example as an insert then it will only add to the table providing
there was no unique key violations. You may want to either add a column with
a rowversion data type, or a datetime column with a default of getdate() or
and identity column.
CREATE TABLE ALLServerDrives ( id int not null identity(1,1),
servername sysname, drive char(1), size bigint )
The identity column will increment for each row inserted and you do not need
to reference it in the insert statement. You may need to order the
information when you insert it into the desination table to make sure the
order is more meaningful! More information on all of these datatypes is in
books online.
John
"D''Animal" wrote:
> John,
> How do I generate unique value (such as date and/or automatically generated
> numbers 1,2,3,4...) as new column so it will not overwrite old entries
> when it executes?
> Thanks
> -D
> "John Bell" wrote:
> > 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