SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ListAvailableSQLServers (SQL-DMO) deprecated


ListAvailableSQLServers (SQL-DMO) deprecated

Author
Message
judy.keadle
judy.keadle
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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();
RSP
RSP
SSC Veteran
SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)

Group: General Forum Members
Points: 292 Visits: 623
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



mrdenny
mrdenny
SSC Veteran
SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)

Group: General Forum Members
Points: 235 Visits: 222
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
}


Aaron Nelson
Aaron Nelson
Old Hand
Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)

Group: General Forum Members
Points: 391 Visits: 173
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

@SQLvariantI have a PowerShell script for you.
mike_walsh
mike_walsh
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 Visits: 483
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
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28469 Visits: 39967
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mrdenny
mrdenny
SSC Veteran
SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)

Group: General Forum Members
Points: 235 Visits: 222
Anything which uses SMO will only return running instances, which are not set to hidden.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search