Add to Technorati Favorites
Welcome to ThePowerShellGuy.com Sign in | Join | Help

PowerShell get-SqlServerList

A handy onliner Function to get a list of SQL servers I have in my Profile

function get-SqlServerList {[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()}

This function retrieves a DataTable containing information about all visible SQL Server 2000 or SQL Server 2005 instances.

 

PoSH> function get-SqlServerList {[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()}                                     
PoSH> get-SqlServerList 

ServerName                         InstanceName                       IsClustered                        Version 
----------                         ------------                       -----------                        ------- 
MOWMS002                                                              No                                 8.00.194 
MOWDT010                                                              No                                 9.00.2047.00 
MOWDT015                           SQLEXPRESS                         No                                 9.00.1399.06 
MOWDT036                                                              No                                 8.00.194 
MOWMS001                                                              No                                 8.00.194 
MOWDT022                           SQLSERVER2005                      No                                 9.00.2047.00
                                                                                                                    
PoSH>

 

Thats all for this post,  

but I have a lot of material about working with dataSets and ADO to fill them from different sources on my old blog

To see how you can connect to a SQL server see this post on my old blog

/\/\o\/\/ PowerShelled: Getting and Working With SQL Server data ...

And working with dataSets

/\/\o\/\/ PowerShelled: More DataTables and joining them in Monad

/\/\o\/\/ PowerShelled: MSH GUI Viewer for Dataset Example

PowerShelled- working with CSV files in MSH (part one)

some utility functions for working with dataSets >

PowerShelled- PowerShell out-DataGrid update and more

and there are more posts about ADO on my old blog.

Enjoy,

Greetings /\/\o\/\/

Published Friday, February 09, 2007 1:52 PM by admin
Filed under: , ,

Comments

# re: PowerShell get-SqlServerList

As I struggle to do SMO and PowerShell (instead of bailing for the familiar Perl/FreeTDS), one thing I've been looking for is a way to enumerate  the instances, while running unattended scripts.

This was the closest thing yet, but it seems to only look on the subnet of the machine it runs on.  So all I got listed was a rogue Express installation, not the instances of the centralized Enterprise servers.  

If you get a notion to show how to query the SQL Browser in PowerShell, I'd be interested.  Overall, since most SMO scripts are VB based, and I'm not a VB scripter, I'm finding it tough to do the transformations - getting the hooks into the needed objects being the least obvious part to me.   So I'll eat up any example of PowerShell using SMO, that go beyond "connect to a hard coded datasource and execute a hard coded query".

thanks for the examples so far

roger reid

Friday, February 15, 2008 12:34 PM by rreid

# listing all the Databases from a SQL Server from PowerShell

I was reading this thread http://web2.minasi.com/forum/topic.asp?TOPIC_ID=25963 , that discusses retrieving

Thursday, February 28, 2008 9:27 AM by The PowerShell Guy

# re: PowerShell get-SqlServerList

I know I'm living in the past, but without the .NET on the end, I tend to think of ADO as the COM library contained in msado15.dll.  While reading "PowerShell in Action", I was excited by the prospect of having a type adapter that would make "classic" ADO more friendly.  I have no problems switching to ADO.NET (although I may have to write some shortcuts to quickly build a DataSet from a connection string and command text), but I happen to have a large body of VBScript that uses "classic" ADO.  In the interest of expedience (not always the best motivation ;-) I was going to continue to use it in my converted PowerShell scripts.

As an exercise, maybe I will write a type adapter for "classic" ADO.

Wednesday, July 30, 2008 2:06 PM by tomtrias
Anonymous comments are disabled