Run a query against registered servers and insert results into table

Question

Run a query against registered servers and insert results into table

Staggerlee

SSCertifiable

Points: 5272
More actions
March 2, 2012 at 3:52 am

#257811

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

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

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

Divine Flame SSCoach Points: 15943 More actions · Answer 1

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 More actions · Answer 2

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: 37370 More actions · Answer 3

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 More actions · Answer 4

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: 37370 More actions · Answer 5

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 More actions · Answer 6

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: 37370 More actions · Answer 7

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 SSC Eights! Points: 914 More actions · Answer 8

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: 15943 More actions · Answer 9

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 More actions · Answer 10

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: 12125 More actions · Answer 11

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 More actions · Answer 12

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

mhorner 67968 SSC Enthusiast Points: 196 More actions · Answer 13

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 More actions · Answer 14

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.