Introduction
SQL Server 2000 and former included SQL-DMO (Distributed Management Object) a COM based object model, to provide programmatic interface for database management. SQL Server 2005 replaces SQL-DMO with two .NET based object libraries;
SQL Server Management Object (SMO) and
Replication Management Object (RMO). This article illustrates how to use SMO to programmatically find available instances of SQL Server from Visual Basic .NET 2005 application. You need to reference
Microsoft.SqlServer.Smo.dll (.NET assembly) that provides the main SMO classes.
Create a Visual Basic 2005 Console Application
[ol]
[li]In Visual Basic 2005 IDE click File --> New Project.[/li]
[li]In resulting New Project dialog box, in the Project Type pane click Visual Basic node.[/li]
[li]In the Templates pane, choose Console Application and click OK.[/li]
[li]In Solution Explorer right click ConsoleApplication1 solution and choose Add References from the context menu.[/li]
[li]In the resulting Add Reference dialog box, under .NET tab, choose Microsoft.SqlServer.Smo and click OK.[/li]
[li]Microsoft.SqlServer.Smo will be added to References node in Solution Explorer under ConsoleApplication1 solution.[/li]
[li]Import SMO namespaces with the Imports statement.
[blue]Imports[/blue] Microsoft.SqlServer.Management.Smo[/li]
[li]Add the following code to Main procedure
Code:
Dim dtlSQLServers As DataTable
' Get list of all available servers.
dtlSQLServers = SmoApplication.EnumAvailableSqlServers(False)
' Display the list of all available servers and
' identify the local sql server.
For Each drServer As DataRow In dtlSQLServers.Rows
If drServer("IsLocal") = True Then
Console.WriteLine("{0} (is the local sql server.)", drServer("Name"))
Else
Console.WriteLine(drServer("Name"))
End If
Next
' Wait
Console.ReadLine()
[/li]
[li]Run the application. List of available SQL Servers will be displayed, with local SQL Server identified.[/li]
[/ol]
EnumAvailableSqlServers public method of
SmoApplication class enumerates a list of available instances of SQL Server.
References
For more information on
SMO Namespace visit
http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.aspx
For more information on [bSmoApplication[/b] class visit
http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.smoapplication.aspx
For more information on
SMO Object Model visit
http://msdn2.microsoft.com/en-us/library/ms162151.aspx
For
SMO Object Model Diagram visit
http://msdn2.microsoft.com/en-us/library/ms162209.aspx
