EnumAvailableSqlServers and passing the name of the servers

[jtammyg]

Hi!

I have the following snippet that gives the name of the sql servers
installed:


Private Sub MainForm_Load(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles MyBase.Load
'just list local servers, set to false if you want to see all
servers
Dim dataTable = SmoApplication.EnumAvailableSqlServers(False)
lstServers.ValueMember = "Name"
lstServers.DataSource = dataTable
End Sub



We have only 2 sql server where the client data reside, but then we
have instances of sql installed in other machines.

I only want to be able to show in my listbox for the user to choose,
those with the sql part in their name.

e.g. tg-ny-sql001
tg-ny-sql002

so the condition would be if the name has sql in it show it in the
listbox.

How can I achieve this with VB 2008 and using SQL Server 2000 in the
first server and SQL Server 2005 in the second server?

Thanks in advanced!

Tammy

 

[Cor Ligthert[MVP]]

Tammy,

In my idea is that what you need. There is as well a tip for a sqlservers on
one machine.

http://www.vb-tips.com/ListComputersAPI.aspx

Cor

 

[jtammyg]

Cor,

Thank you for your reply, but that does not help me.

I have one listbox that should show me only the sql server that i want

Then i have another listbox which will show me the databases from the
server selected in the previous listbox.

Then I have a third listbox with specific book titles from the
databases selected above.


Here is the code that I have for now:



Imports System
Imports System.Data.SqlClient
Imports System.Collections
Imports System.Data
Imports Microsoft.SqlServer
Imports Microsoft.SqlServer.Server
Imports Microsoft.SqlServer.Management
Imports Microsoft.SqlServer.Management.Smo


Public Class Form1

Private Sub MainForm_Load(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles MyBase.Load
'just list local servers, set to false if you want to see all
servers
Dim dataTable = SmoApplication.EnumAvailableSqlServers(False)
lstServers.ValueMember = "Name"
lstServers.DataSource = dataTable
End Sub

Private Sub lstServers_SelectedIndexChanged(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
lstServers.SelectedIndexChanged
lstDatabases.Items.Clear()

If lstServers.SelectedIndex <> -1 Then
Dim serverName As String =
lstServers.SelectedValue.ToString()
Dim server As Server = New Server(serverName)
Try
For Each database As Database In server.Databases
lstDatabases.Items.Add(database.Name)
Next

Catch ex As Exception
Dim exception As String = ex.Message
End Try
End If
End Sub

Private Sub lstDatabases_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles lstDatabases.Click

lstFileSets.Items.Clear()

Dim cn As New SqlClient.SqlConnection()
Dim cm As New SqlClient.SqlCommand()
Dim dr As SqlClient.SqlDataReader


With cn
.ConnectionString = "Data Source=" &
lstServers.SelectedValue & ";Initial Catalog=" &
lstDatabases.SelectedValue & ";Integrated Security=SSPI"
.Open()
End With


With cm
.CommandText = "usp_Search_book_titles"
.CommandType = CommandType.StoredProcedure
.Connection = cn
.Parameters.AddWithValue("@Matter",
lstDatabases.SelectedItem)
End With

dr = cm.ExecuteReader(CommandBehavior.CloseConnection)


While dr.Read
lstFileSets.Items.Add(dr.Item(0))
End While


dr.Close()
End Sub


End Class



Unfortunately I need to only show the 2 sql servers and NOT all the
instances in which sql is installed.

sql server names:

e.g. tg-ny-sql001
tg-ny-sql002



I tried incorporating what you suggested but I get a ton of errors. I
am using VB 2008.

Thanks a lot in advanced!!!!

Tammy

 

[Cor Ligthert[MVP]]

Tammy,

Have a look at your subject, I see not any relation with what you write and
that subject, I was answering on your subject.

If you do not step one first the second step can never be done.

Cor

 

[jtammyg]

Cor,

As I have it now..it does work, but I do not want the users to see the
rest of the available instances of SQL installed throughout the
network. They only need to see the 2 servers from which they will
select the database etc.

Thanks!

Sorry I am new to VB! :-(((

Tammy