|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Friday, June 07, 2013 7:29 AM
Points: 1,
Visits: 15
|
|
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();
|
|
|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: Thursday, May 30, 2013 8:44 AM
Points: 172,
Visits: 479
|
|
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
|
|
|
|
|
Valued Member
      
Group: General Forum Members
Last Login: Sunday, December 09, 2012 11:47 PM
Points: 60,
Visits: 169
|
|
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 }
|
|
|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: Tuesday, February 12, 2013 12:54 PM
Points: 41,
Visits: 120
|
|
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.
|
|
|
|
|
SSC Journeyman
      
Group: General Forum Members
Last Login: Friday, January 11, 2013 9:05 AM
Points: 89,
Visits: 408
|
|
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
|
|
|
|
|
SSChampion
        
Group: General Forum Members
Last Login: Today @ 9:24 AM
Points: 11,791,
Visits: 28,073
|
|
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
|
|
|
|
|
Valued Member
      
Group: General Forum Members
Last Login: Sunday, December 09, 2012 11:47 PM
Points: 60,
Visits: 169
|
|
| Anything which uses SMO will only return running instances, which are not set to hidden.
|
|
|
|