Run a query against registered servers and insert results into table

  • Staggerlee

    SSCertifiable

    Points: 5272

    Hi all,

    Is it possible to run a query against multiple registered servers and have the results inserted into a table?

    Trying to get details of SQL boxes and wanted to keep the data dynamic and always up to date.

    So id like to run say

    SELECT @@VERSION AS [SQL Server and OS Version Info];

    Thanks

    S

  • Divine Flame

    SSCoach

    Points: 15941

    stebennettsjb (3/2/2012)


    Hi all,

    Is it possible to run a query against multiple registered servers and have the results inserted into a table?

    Trying to get details of SQL boxes and wanted to keep the data dynamic and always up to date.

    So id like to run say

    SELECT @@VERSION AS [SQL Server and OS Version Info];

    Thanks

    S

    Have you tried SSIS? It is a good tool to perform these kind of tasks.


    Sujeet Singh

  • ard5karthick

    SSCommitted

    Points: 1603

    Gan you explain a Bit...

    This Is Possible Using Linked Server's But cannot say If this is what you are expecting, Explain your need?

  • MarkusB

    SSC-Dedicated

    Points: 37369

    There are a couple of options how you can do this and the best choice will probably depend on what exactly you try to achieve.

    With SQL 2008 you have the option of creating a central management server and the run a query from that server on all registered servers or a group of servers.

    http://www.sqlskills.com/BLOGS/KIMBERLY/post/SQL-Server-2008-Central-Management-Servers-have-you-seen-these.aspx

    Other options would be using linked servers or powershell.

    [font="Verdana"]Markus Bohse[/font]

  • Staggerlee

    SSCertifiable

    Points: 5272

    Hi,

    Divine Flame:

    thanks i was aware of doing it in ssis, was hoping there might be a simpler way with TSQL for one off type events.

    ard5karthick:

    Id like to say run the query for server info on 5 servers at once and get the results put into a table. Running the query i can do.. But how to put it into a table is my question/

    MarkusB:

    Thanks for the link, i had found it while doing my own searching, but i hadnt found a way to insert into a table..

    Thanks for the replies

    S

  • MarkusB

    SSC-Dedicated

    Points: 37369

    stebennettsjb (3/6/2012)


    Hi,

    MarkusB:

    Thanks for the link, i had found it while doing my own searching, but i hadnt found a way to insert into a table..

    S

    What exactly is your problem? If I run the following code the Insert works just fine for all the selected instances.

    CREATE TABLE #T

    (Server sysname, Version nvarchar(2000))

    go

    INSERT INTO #t (Server, Version)

    select @@ServerName,@@version

    [font="Verdana"]Markus Bohse[/font]

  • Staggerlee

    SSCertifiable

    Points: 5272

    Hi MarkusB,

    Thanks im properly being dim..

    But wouldn't that create a temp table on every server, with the results of only the server its created on. I would like to run a query against the servers and store all results into 1 table on a specific server.

    Thanks

    S

  • MarkusB

    SSC-Dedicated

    Points: 37369

    stebennettsjb (3/6/2012)


    Hi MarkusB,

    But wouldn't that create a temp table on every server, with the results of only the server its created on. I would like to run a query against the servers and store all results into 1 table on a specific server.

    S

    Oeps, of course your right.

    Using this technique of querying multiple servers is very convenient if you want to do a quick check on multiple servers, but if you want to collect data for some kind of monitoring purpose I would use different options. As I said in first post, it all depends on what exactly you try to achieve. I don't think it's just a check of versions you're after.

    [font="Verdana"]Markus Bohse[/font]

  • jmadsen 98862

    Mr or Mrs. 500

    Points: 591

    Yes, that will create a temp table on each server with 1 row.

    If this is a one off type run and there are not an unusually high number of servers; just run the select without putting in a table and review the results that you get. "one off type run" sounds like something you're not trying to save in a table anyway, but just want to review the versions on each server.

  • Divine Flame

    SSCoach

    Points: 15941

    stebennettsjb (3/6/2012)


    Hi MarkusB,

    Thanks im properly being dim..

    But wouldn't that create a temp table on every server, with the results of only the server its created on. I would like to run a query against the servers and store all results into 1 table on a specific server.

    Thanks

    S

    I am not sure if you have found the solution for this, but using OPENQUERY or Link Server you can do that. Since it is one off event you may want to go with OPENQUERY.

    1. Create a table on any one server (say ServerA) you like.

    2. Use OPENQUERY on ServerA to get the data from all the other servers.

    Please post here if you face any problem in this.


    Sujeet Singh

  • melanie.gaff

    SSC Journeyman

    Points: 81

    I am trying to do the exact same thing. I don't see that any of these suggestions will result in the solution you are after. If you come up with something, I would be interested in knowing the solution. I'm currently stuck with running the queries through the Registered Servers option and then saving the results and importing them to tables in a database. Very awkward, but feasible.

  • Dan.Humphries

    SSChampion

    Points: 12123

    Just a thought but if you have to do more work to avoid creating an SSIS as a "on off" then it is still worth creating the SSIS even if you have no intention of ever running it again.

    Dan

    If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

  • Guillaume Hermans-172197

    SSC Journeyman

    Points: 75

    I wonder if you found a solution. I'm also very interested.

  • mhorner 67968

    SSC Enthusiast

    Points: 196

    The only way I see to do this is to create a linked server and write the individual results into the table using a linked server.

  • Staggerlee

    SSCertifiable

    Points: 5272

    Hi,

    So many years later, im not sure what i did back then, but now i just use PowerShell. load SQLPS module connect to the CMS Server query the msdb to get the list of servers and then loop through saving the results to a table or excel or what ever i need.

    I can supply a example script if anyone wants something to start with.

Viewing 15 posts - 1 through 15 (of 20 total)

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