EnumAvailableSqlServers and passing the name of the servers

  • Thread starter Thread starter jtammyg
  • Start date Start date
J

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,

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
 
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
 
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
 
Back
Top