SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Run a query against registered servers and insert results into table


Run a query against registered servers and insert results into table

Author
Message
Staggerlee
Staggerlee
SSChasing Mays
SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)

Group: General Forum Members
Points: 634 Visits: 1139
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
Divine Flame
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2289 Visits: 2816
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
ard5karthick
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 Visits: 162
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
MarkusB
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5839 Visits: 4208
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.

Markus Bohse
Staggerlee
Staggerlee
SSChasing Mays
SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)

Group: General Forum Members
Points: 634 Visits: 1139
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
MarkusB
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5839 Visits: 4208
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



Markus Bohse
Staggerlee
Staggerlee
SSChasing Mays
SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)

Group: General Forum Members
Points: 634 Visits: 1139
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
MarkusB
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5839 Visits: 4208
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.

Markus Bohse
jmadsen 98862
jmadsen 98862
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 692
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
Divine Flame
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2289 Visits: 2816
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search