Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

insert data using dynamic open row set Expand / Collapse
Posted Monday, December 6, 2010 5:05 AM


Group: General Forum Members
Last Login: Friday, February 4, 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.*
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)

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



Group: General Forum Members
Last Login: Thursday, November 24, 2016 8:14 AM
Points: 1,844, Visits: 3,403
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.

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

Add to briefcase

Permissions Expand / Collapse