Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Run a query against registered servers and insert results into table Expand / Collapse
Author
Message
Posted Friday, March 02, 2012 3:52 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 3:47 AM
Points: 314, Visits: 730
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
Post #1260704
Posted Monday, March 05, 2012 6:28 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:28 PM
Points: 1,266, Visits: 2,318
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
Post #1261541
Posted Tuesday, March 06, 2012 6:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 27, 2014 6:05 AM
Points: 43, 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?
Post #1262150
Posted Tuesday, March 06, 2012 7:00 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, April 03, 2014 8:27 AM
Points: 4,432, Visits: 4,155
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
Post #1262156
Posted Tuesday, March 06, 2012 7:31 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 3:47 AM
Points: 314, Visits: 730
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
Post #1262182
Posted Tuesday, March 06, 2012 7:37 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, April 03, 2014 8:27 AM
Points: 4,432, Visits: 4,155
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
Post #1262187
Posted Tuesday, March 06, 2012 7:49 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 3:47 AM
Points: 314, Visits: 730
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
Post #1262209
Posted Tuesday, March 06, 2012 8:39 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, April 03, 2014 8:27 AM
Points: 4,432, Visits: 4,155
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
Post #1262264
Posted Tuesday, March 06, 2012 8:59 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 10:47 AM
Points: 50, Visits: 596

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.
Post #1262300
Posted Monday, March 12, 2012 2:23 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:28 PM
Points: 1,266, Visits: 2,318
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
Post #1265010
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse