Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

insert data using dynamic open row set Expand / Collapse
Author
Message
Posted Monday, December 06, 2010 5:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 04, 2011 1:22 AM
Points: 15, Visits: 42
Hi all,
i have 20 servers in each server we have sp with name ].[SP_SQL_Server_Info_HADB_ToReport].

TO INSERT SP RESULTS INTO ONE CENTRAL SERVER table i used fallowing query.
use master
Insert Into dbo.Server_Details_Table
select A.*
FROM
OPENROWSET ('SQLNCLI10', 'server=HADB;trusted_connection=yes',
'set fmtonly off exec [MASTER].[dbo].[SP_SQL_Server_Info_HADB_ToReport]')as A

NOW my query is how tochange above query to insert sp results from all servers(i.e dynamic open row set based on servername)

with 1) window s authentication(to server)
2) SQL AUTHENTICATION (to server)

thanks in advance
Sridevi
Post #1030533
Posted Monday, December 06, 2010 11:07 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 1:33 PM
Points: 1,259, Visits: 2,386
One way to accomplish this would be to create a linked server for all servers you would like this SP to run on then create a cursor that will loop through all names in sys.servers and use the name to in dynamic sql to create the insert-exec statements. By creating linked servers, you will not have to use openrowset either, just basic insert-exec

insert into spresults
exec [Linked_server].[MASTER].[dbo].[SP_SQL_Server_Info_HADB_ToReport]

If you do not want to create the linked servers then you can create a table that will have all your server names, and just have cursor loop through that, using your openrowset query.


Bob
-----------------------------------------------------------------------------
How to post to get the best help
Post #1030742
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse