Post reply

insert data using dynamic open row set

  • December 6, 2010 at 5:05 am

    #243603

    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

  • December 6, 2010 at 11:07 am

    #1259136

    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[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply