Run a query against registered servers and insert results into table

  • 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

  • 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

  • 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?

  • 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]

  • 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

  • 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]

  • 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

  • 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]

  • 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.

  • 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

  • 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.

  • 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.

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

  • 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.

  • 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 19 total)

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