<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Programming
»
Powershell
»
Query local SSMS server group with...
Query local SSMS server group with Powershell?
Rate Topic
Display Mode
Topic Options
Author
Message
dtibz01
dtibz01
Posted Thursday, December 06, 2012 10:53 AM
Forum Newbie
Group: General Forum Members
Last Login: 2 days ago @ 7:58 AM
Points: 5,
Visits: 133
This may be a totally off the wall question, but it seems like it should be possible to do. Just can't find any specifics as to how I might accomplish it.
I have a situation in that I have the same database (structurally) across multiple SQL Server instances, with different database names. Through SSMS I have a local server group defined that references each unique instance and individual database. This allows me query each database at once with a single SQL statement, regardless of database name.
When queries are run through my SSMS environment, I have the option enabled to return the SQL Server instance in the grid output so the instance name is returned with each row. I would like to automate running my query and outputting the results to Excel via Powershell script.
What I'm hung up on is that I need to be able (with Powershell) to reference my SSMS local server group as the the data source that the query runs against, and run the query. Has anyone done this?
I appreciate any feedback..
Post #1393651
Gazareth
Gazareth
Posted Friday, December 07, 2012 9:02 AM
UDP Broadcaster
Group: General Forum Members
Last Login: Wednesday, May 15, 2013 6:22 AM
Points: 1,474,
Visits: 2,341
Well, if you can get the information out of the file, registered servers are stored in
C:\Users\username\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\RegSrvr.xml
But I'd be much more tempted to set up my own text file or db table to store the server/database information.
Easy enough once you've got the list to run your query in a foreach loop.
Post #1394105
opc.three
opc.three
Posted Monday, December 24, 2012 1:34 PM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 7:22 AM
Points: 6,693,
Visits: 11,707
Have you heard of
Central Management Server
(CMS)? It supports the "interact with each server in a server group from PowerShell" functionality that I think you are after.
If setting up a CMS is not something you want to begin doing then I would also look into storing the list of servers in a text file and using something like
foreach ($server in (Get-Content C:\myservers.txt)){Write-Host $server;}
or something similar with the servers stored in a utility database somewhere instead of a text file.
__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Believe you can and you're halfway there.
--Theodore Roosevelt
Everything Should Be Made as Simple as Possible, But Not Simpler
--Albert Einstein
The significant problems we face cannot be solved at the same level of thinking we were at when we created them.
--Albert Einstein
1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples.
--Giordy
Post #1399988
dtibz01
dtibz01
Posted Wednesday, December 26, 2012 6:46 AM
Forum Newbie
Group: General Forum Members
Last Login: 2 days ago @ 7:58 AM
Points: 5,
Visits: 133
Thank you both (Gazareth & opc.three) for taking the time to reply. I will likely end up going the route of a servers.txt file, I was just trying to find a way around it and in typical fashion was probably over thinking it a bit I guess. In any case, I do appreciate the feedback.
DT
Post #1400230
MG-148046
MG-148046
Posted Thursday, December 27, 2012 6:40 AM
SSCommitted
Group: General Forum Members
Last Login: Yesterday @ 5:57 AM
Points: 1,620,
Visits: 1,923
You could use the stucture available in SQLPS. Set your location to SQLSERVER:\SQLRegistration\Database Engine Server Group\<your group name> then set a variable equal to get-childitem and do a foreach loop over each server in the group.
A simple example:
Set-Location SQLSERVER:\SQLRegistration\Database Engine Server Group\<your group name>
$servers = get-childitem
foreach ($server in $server) {<do something herer>}
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
Post #1400598
opc.three
opc.three
Posted Thursday, December 27, 2012 8:54 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 7:22 AM
Points: 6,693,
Visits: 11,707
MG-148046 (12/27/2012)
You could use the stucture available in SQLPS. Set your location to SQLSERVER:\SQLRegistration\Database Engine Server Group\<your group name> then set a variable equal to get-childitem and do a foreach loop over each server in the group.
A simple example:
Set-Location SQLSERVER:\SQLRegistration\Database Engine Server Group\<your group name>
$servers = get-childitem
foreach ($server in $server) {<do something herer>}
I like the text file or database table only because it's portable. Here is more info on it. It requires the installation of some add-ons to make sqlps importable into your PoSh sessions.
Programming SQLRegistration in the SQL Server PowerShell Provider
__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Believe you can and you're halfway there.
--Theodore Roosevelt
Everything Should Be Made as Simple as Possible, But Not Simpler
--Albert Einstein
The significant problems we face cannot be solved at the same level of thinking we were at when we created them.
--Albert Einstein
1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples.
--Giordy
Post #1400654
Gazareth
Gazareth
Posted Wednesday, January 02, 2013 4:50 AM
UDP Broadcaster
Group: General Forum Members
Last Login: Wednesday, May 15, 2013 6:22 AM
Points: 1,474,
Visits: 2,341
MG-148046 (12/27/2012)
You could use the stucture available in SQLPS. Set your location to SQLSERVER:\SQLRegistration\Database Engine Server Group\<your group name>
Nice, didn't know about that location! Certainly easier than parsing the XML file!
Post #1401777
« Prev Topic
|
Next Topic »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
