November 17, 2009 at 8:30 am
Hello everybody, I wondered if in Tsql (sqlserver2005) there is a way to select all the instances i registered in SSMS registered servers.
I can export them and then i have an xml-file, but the information, is it also stored in system-tables, and if so, where, and how to access them?
thank you!
Lukas
November 17, 2009 at 8:43 am
Are you asking about creating a view to select from master.sys.servers?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 17, 2009 at 8:58 am
thank you for the reply, but:
No this only gives the local instances of the machine i'm connected to (at least that's what i get when i tried it), i want to see the servers i registered with "registered servers" in sql server management studio (ctrl-alt-G to view them there)
November 17, 2009 at 9:04 am
My mistake. I was thinking of linked servers.
No, registered servers isn't something that's stored in the database. It's local application data.
You can export your registered server data into a file. You might be able to parse out the file into either XML or relational data that can be viewed through OpenRowset.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 17, 2009 at 9:07 am
Ok thank you Gsquared, at least now i know it's not possible directly in tsql !
Lucas
November 17, 2009 at 1:50 pm
It is not possible from T-SQL, but if you download the Powershell provider for SQL Server 2008 and Powershell, you can get to that information easily.
From the Powershell prompt, after including the SQL Server provider:
PS> Set-Location SQLSERVER:
SQLSERVER:PS> Get-ChildItem
Name Root Description
---- ---- -----------
SQL SQLSERVER:\SQL SQL Server Database Engine
SQLPolicy SQLSERVER:\SQLPolicy SQL Server Policy Management
SQLRegistration SQLSERVER:\SQLRegistration SQL Server Registrations
DataCollection SQLSERVER:\DataCollection SQL Server Data Collection
PS> Set-Location .\SQLRegistration
PS> Get-ChildItem
Directory: SqlServerProviderSnapin100\SqlServer::SQLSERVER:\SQLRegistration
Mode Name
---- ----
d Central Management Server Group
d Database Engine Server Group
PS> Set-Location '.\Database Engine Server Group'
PS> Get-ChildItem
Directory: SqlServerProviderSnapin100\SqlServer::SQLSERVER:\SQLRegistration\Database Engine Server Group
Mode Name
---- ----
- serverA
- serverB\developer2005
- serverB\developer2008
d Group A
d Group B
PS>
If you install the client tools for SQL Server 2008 - the provider is included and you can launch Powershell from within SSMS (right-click on object in Object Explorer and select Start Powershell).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 17, 2009 at 3:18 pm
Thank you Jeffrey! I will check out the provider for sqlServer 2008 as soon as I can.
Lucas
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply