EnumAvailableSq lServers 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(B yVal sender As System.Object, ByVal e
As System.EventArg s) Handles MyBase.Load
'just list local servers, set to false if you want to see all
servers
Dim dataTable = SmoApplication. EnumAvailableSq lServers(False)
lstServers.Valu eMember = "Name"
lstServers.Data Source = 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

<jt*****@gmail. comschreef in bericht
news:50******** *************** ***********@p25 g2000hsf.google groups.com...

Hi!

I have the following snippet that gives the name of the sql servers
installed:
Private Sub MainForm_Load(B yVal sender As System.Object, ByVal e
As System.EventArg s) Handles MyBase.Load
'just list local servers, set to false if you want to see all
servers
Dim dataTable = SmoApplication. EnumAvailableSq lServers(False)
lstServers.Valu eMember = "Name"
lstServers.Data Source = 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

[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.Sql Client
Imports System.Collecti ons
Imports System.Data
Imports Microsoft.SqlSe rver
Imports Microsoft.SqlSe rver.Server
Imports Microsoft.SqlSe rver.Management
Imports Microsoft.SqlSe rver.Management .Smo
Public Class Form1

Private Sub MainForm_Load(B yVal sender As System.Object, ByVal e
As System.EventArg s) Handles MyBase.Load
'just list local servers, set to false if you want to see all
servers
Dim dataTable = SmoApplication. EnumAvailableSq lServers(False)
lstServers.Valu eMember = "Name"
lstServers.Data Source = dataTable
End Sub

Private Sub lstServers_Sele ctedIndexChange d(ByVal sender As
System.Object, ByVal e As System.EventArg s) Handles
lstServers.Sele ctedIndexChange d
lstDatabases.It ems.Clear()

If lstServers.Sele ctedIndex <-1 Then
Dim serverName As String =
lstServers.Sele ctedValue.ToStr ing()
Dim server As Server = New Server(serverNa me)
Try
For Each database As Database In server.Database s
lstDatabases.It ems.Add(databas e.Name)
Next

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

Private Sub lstDatabases_Cl ick(ByVal sender As System.Object,
ByVal e As System.EventArg s) Handles lstDatabases.Cl ick

lstFileSets.Ite ms.Clear()

Dim cn As New SqlClient.SqlCo nnection()
Dim cm As New SqlClient.SqlCo mmand()
Dim dr As SqlClient.SqlDa taReader
With cn
.ConnectionStri ng = "Data Source=" &
lstServers.Sele ctedValue & ";Initial Catalog=" &
lstDatabases.Se lectedValue & ";Integrate d Security=SSPI"
.Open()
End With
With cm
.CommandText = "usp_Search_boo k_titles"
.CommandType = CommandType.Sto redProcedure
.Connection = cn
.Parameters.Add WithValue("@Mat ter",
lstDatabases.Se lectedItem)
End With

dr = cm.ExecuteReade r(CommandBehavi or.CloseConnect ion)
While dr.Read
lstFileSets.Ite ms.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