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

ListAvailableSQLServers (SQL-DMO) deprecated Expand / Collapse
Author
Message
Posted Thursday, April 28, 2011 10:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 23, 2014 6:14 AM
Points: 1, Visits: 21
I understand that ListAvailableSQLServers is deprecated, but I cannot find what to replace it with.
I see that SQL-DMO is replaced with SMO, but I do not see an equivalent method. code snippet below.

Assistance greatly appreciated.

SQLDMO.NameList oNames;
SQLDMO.Application oSQLApp = new SQLDMO.Application();
oNames = oSQLApp.ListAvailableSQLServers();
Post #1100395
Posted Thursday, April 28, 2011 10:47 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 8:57 AM
Points: 172, Visits: 570
judy -good question. You can derive this information, but will have to use Powershell. Aaron Nelson, aka, SQLVariant posted a blog to do just this -

http://sqlvariant.com/wordpress/index.php/2010/09/finding-sql-servers-with-powershell/

Hope this helps!

Robert Pearl
http://www.pearlknows.com



Post #1100402
Posted Thursday, April 28, 2011 12:10 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, October 17, 2014 9:18 AM
Points: 77, Visits: 217
If you want to find all the instances on the AD domain, hidden or not you can use the code which Aaron and I put together for my book "Securing SQL Server". It'll do a dump from AD for all the machines in the domain then go through the list looking for machines which have the SQL Server services installed. As long as the machine is turned on and connected to the network this script will find the services. This does need to be run by a domain admin.

$objDomain = New-Object System.DirectoryServices.DirectoryEntry
$objSearcher = New-Object System.DirectoryServices.DirectorySearcher
$objSearcher.SearchRoot = $objDomain
$objSearcher.Filter = ("computer")
$objSearcher.PropertiesToLoad.Add("name")
$Computers = $objSearcher.FindAll()
foreach ($machine_name in $Computers | sort computername )
{
$sql_servers = get-wmiobject -class win32_service -computer $machine_name
$sql_servers | where { $_.name -like 'MSSQL$' -or $_.name -eq 'MSSQLSERVER'} | select name
}

Post #1100455
Posted Thursday, April 28, 2011 1:06 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 3:11 PM
Points: 41, Visits: 124
Thanks Robert. Here's the code that I posted in the second blog I did on that. I find it slightly more useful although SQL Browser has some funny impact on it every once in a while:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null

[Microsoft.SqlServer.Management.Smo.SmoApplication]::EnumAvailableSqlServers() | Export-Csv C:\Temp\LookWhatElseIFound.csv


@SQLvariant
I have a PowerShell script for you.
Post #1100518
Posted Thursday, April 28, 2011 2:34 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, December 4, 2014 8:11 PM
Points: 87, Visits: 475
I realize this isn't showing you how to do it yourself programatically, but I've always liked using the Microsoft Assessment and Planning (MAP) toolkit. It shows me all instances, organizes them into a very detailed spreadsheet (SQL Version, service pack level, number of CPUs, edition, Windows edition/version, drives with space info, etc.)

I blogged about the tool a couple years ago when I first discovered it (pardon the excitement in the post ) Downfalls are - it has to be installed, Office 2007 needed and you need to have higher permissions (Preferably domain admin, especially if you don't know what boxes SQL is installed on) but you can always have a sysadmin run it and then you can use that Spreadsheet to list your SQL instances and even import it into a database used for database administration.

http://www.straightpathsql.com/archives/2009/02/what-sql-instances-are-installed-on-my-network/



__________________________________________________

Mike Walsh
SQL Server DBA
Blog - www.straightpathsql.com/blog |Twitter
Post #1100606
Posted Thursday, April 28, 2011 2:49 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 12,962, Visits: 32,501
and yet another way..this doesn't return the "hidden" servers denny identified; just the items that are advertised via SQL browser, i think.

this is how to do it in .NET using an SMO object:

--Imports Microsoft.SqlServer.Management
--Imports Microsoft.SqlServer.Management.Smo
Dim myDt As DataTable = Smo.SmoApplication.EnumAvailableSqlServers(False)
Dim a() As DataRow = myDt.Select("", "[Server] asc")
--just so you can see the columns collection
For Each dr As DataRow In a
Debug.Print(dr!Name)
Debug.Print(dr!Server)
Debug.Print(dr!Instance)
Debug.Print(dr!IsClustered)
Debug.Print(dr!Version)
Debug.Print(dr!IsLocal)
Next



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1100621
Posted Thursday, April 28, 2011 2:55 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, October 17, 2014 9:18 AM
Points: 77, Visits: 217
Anything which uses SMO will only return running instances, which are not set to hidden.
Post #1100627
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse