Determine if SQL server is on network in VB.NET 2003

[DougP]

I am writing an app which can use either Access or SQL server as a database.
I want to know how to show if any SQL servers are present similar to the way the ODBC driver does when you want to make a DSN to SQL server.


TIA

DougP, MCP, A+

 

[Durkin]

Use the sqldmo libraries. There is a class called SQLDMO.Application with a collection called ListAvailableSQLServers.

Durkin

 

[DougP]

Ok I found this code in Help

Dim sqlApp As New SQLDMO.Application
Dim NL As SQLDMO.NameList

Set NL = sqlApp.ListAvailableSQLServers
For index = 1 To NL.Count
    cboServers.AddItem NL.Item(index)
Next

But the [blue]SQLDMO.Application[/blue] is blue underline
What else do I need?
[red]Imports [/red]"something"
Or

DougP, MCP, A+

 

[SqueakinSweep]

Doug

Add a COM reference to your project
It will probably be Microsoft SQLDMO 8.0


Sweep
...if it works dont mess with it

 

[DougP]

Ok I did that now I get this error
--------------------------------
An unhandled exception of type 'System.InvalidCastException' occurred in CADTime2.exe

Additional information: QueryInterface for interface SQLDMO.NameList failed.
-----------------------
On this line [red]NL = sqlApp.ListAvailableSQLServers[/red]
New code

        Dim sqlApp As New SQLDMO.Application
        Dim NL As SQLDMO.NameList
        Dim a As Integer
        NL = sqlApp.ListAvailableSQLServers
        For a = 1 To NL.Count
            cboServers.Items.Add(NL.Item(a))
        Next

DougP, MCP, A+

 

[DougP]

One note I have SQL server on my PC, running XP Pro
The other SQL server and the Windows 2000 is currently shut off.
Will it find the Occurrence on my local PC?


DougP, MCP, A+

 

[mpalmer78]

Try this instead....

Dim sqlApp As New SQLDMO.Application
Dim NL As SQLDMO.NameList
Dim a As Integer
NL = sqlApp.ListAvailableSQLServers
For a = 1 To NL.Count
cboServers.Items.Add(NL.Item(a).ToString)
Next

(All I added was ".ToString")

I believe you're getting that error message because it's trying to cast a NameListItem to a string...

 

[DougP]

still getting the same error
on same line
NL = sqlApp.ListAvailableSQLServers
----------------
An unhandled exception of type 'System.InvalidCastException' occurred in CADTime2.exe

Additional information: QueryInterface for interface SQLDMO.NameList failed.


DougP, MCP, A+

 

[Lenvdb]

Hi DougP

I am getting the same error using WinXP Pro.

How did you get past it as I am stuck and the wealth of posts on this is not very helpful.

Rgds
Len

Quiet as a bulldozer Mate!

 

[Lenvdb]

Hi DougP - it seems like this interface will only work on Win2000 and WinNT - not on XP!!

Which may explain why we have a problem here...

Anyone ever used this Interface with success?

Len

Quiet as a bulldozer Mate!

 

[DougP]

Although you maybe right it makes no sense since VB.NET 2003 is designed after Windows XP.


DougP, MCP, A+

 

[DougP]

BTW I did not get pasted it, I gave up.

DougP, MCP, A+

 

[bobisto]

You need several pairs of parenthesis
Dim sqlApp As New SQLDMO.Application()
Dim NL As SQLDMO.NameList
Dim a As Integer
NL = sqlApp.ListAvailableSQLServers()
For a = 1 To NL.Count
cboServers.Items.Add(NL.Item(a).ToString())
Next
I've tried the code but in C# and it's working. I use WinXP Prof. but I have only desktop engine of sql 2000 and the code have listed all sql servers on my network.

 

[Lenvdb]

Sorry - it was my Stupidity....
You definitely need Service Pack 2 or later in WinXP to make this work....

I installed it on the server and now it works like a charm....

Len

Quiet as a bulldozer Mate!