>> List of servers

[Jonathan]

Hi, I want to set up a form so that users can change the data source from one
sql database to another. I would like to have a combobox listing network sql
servers and then another combobox listing databases attached to the selected
sql server.

Any ideas or, suggestions and code samples on how to create this are
appreciated

Many thanks
Jonathan

 

[Brendan Reynolds]

Hi, I want to set up a form so that users can change the data source from
one
sql database to another. I would like to have a combobox listing network
sql
servers and then another combobox listing databases attached to the
selected
sql server.

Any ideas or, suggestions and code samples on how to create this are
appreciated

Many thanks
Jonathan

I found this example in the Google newsgroup archives ...

http://groups.google.ie/group/micro...l+dmo+list+available+servers#50f25d95f3056562

It was written for VB6 and SQL Server 7, so I did a bit of testing and got
the following to work for me. This requires a reference to the Microsoft
SQLDMO Object Library ...

Public Function GetAllSqlServerCollection(colSqlServers As Collection) As
Boolean
Dim intIndex As Integer
Dim oApplication As SQLDMO.Application
Dim oNameList As SQLDMO.NameList


Set oApplication = New SQLDMO.Application
With oApplication
Set oNameList = .ListAvailableSQLServers
With oNameList
For intIndex = 1 To .Count
colSqlServers.Add (oNameList.Item(intIndex))
Debug.Print oNameList.Item(intIndex)
Next
End With
End With
Set oApplication = Nothing
GetAllSqlServerCollection = True
End Function

I had to change "New Application" in the original code to "New
SQLDMO.Application", to avoid a conflict with the Access.Application object.

There's one oddity about the result that I can't explain. The result, when I
run this in the Immediate window, looks like this ...

? GetAllSqlServerCollection(new Collection)
(l
a valid server name\instance name
another valid server name\instance name
etc.

In other words, it works, except for that "(1" as the first item. I don't
know where that's coming from. To the best of my knowledge, there shouldn't
be any server with that name on our network - I'm not sure it's even an
alowable name. I wonder if that's actually the digit 1 or the lower-case
letter L?

OK I checked it is a lower-case "L". Hmn .... a truncation of "(local)"
perhaps?

 

[Douglas J. Steele]

If you prefer not setting a reference to SQLDMO, use Late Binding. Change
the code to

Public Function GetAllSqlServerCollection(colSqlServers As Collection) As
Boolean
Dim intIndex As Integer
Dim oApplication As Object
Dim oNameList As Object

Set oApplication = CreateObject("SQLDMO.Application")
With oApplication
Set oNameList = .ListAvailableSQLServers
With oNameList
For intIndex = 1 To .Count
colSqlServers.Add (oNameList.Item(intIndex))
Debug.Print oNameList.Item(intIndex)
Next
End With
End With
Set oApplication = Nothing
GetAllSqlServerCollection = True
End Function

(FWIW, I didn't get any spurious first entry)

 

[Jonathan]

Thanks guys, your solutions are just perfect.

Many thanks,
Jonathan