Getting a list of SQL 2005 Servers and The Databases in them

Aquila
10-02-2006, 09:48 AM
I know it's poor form to use VB6 as a reference for .net/2005 questions, but I really don't know of any other way.

Back using VB 6 I was able to get a list of SQL Servers by using the
SQLDMO.NameList object, from there I could easily get the databases held in each server.

Is there a similar object or method of sql, sqlclient, etc I can use to do the same thing?

I need to detect running SQL Server 2005 databases and the databases attached to them. regardless of if the db is local or on a network, and do that via user interface (I need users to be able to select their databases on different servers from the same app without them knowing how to create their own connection string)

Aquila
10-02-2006, 10:03 AM
AHA! I found it
Microsoft.SQLServer.Management.SMO

is the .net/2005 way to do it

I'll publish a working demo on how to work with it once I figure it all out, I can't be the only one that needs to use this.

Aquila
10-02-2006, 11:25 AM
Here's how you get a list of servers.
//Assume that there is a form with a datagridview object named dgvServers

'this process takes a little time so set the cursor to a wait cursor
Me.Cursor = Cursors.WaitCursor
'The EnumAvailableSQLServers method returns a datatable
'so dim a datatable and execute the method using the second override (show local only) set to false
'so I can see all the databases regardless of if they are on the network or otherwise.
Dim dtbl As DataTable = Microsoft.SqlServer.Management.Smo.SmoApplication.EnumAvailableSqlServ ers(False)
'Pipe the results into a dataview for use.
Me.dgvServers.DataSource = dtbl
'Return the cursor to normal
Me.Cursor = Cursors.Default


You'll also have to add a reference to Microsoft.SqlServer.Management.Smo

Simple enough!

Aquila
10-02-2006, 11:35 AM
aaaaaand....

There is an even simpler way... using the SMO.RegisterdServer class

Someone's already written a much better tutorial than I ever could at http://www.sqldbatips.com/showarticle.asp?ID=45

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum