I need to find an example program or code (VB .NET only) that will
show how to enumerate a list of SQL servers that are available on a
network.  Previously under VB6 it was really only possible (that I
know of) by using DMO.

I have seen examples in C# that show using the netapi32.dll API
(http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=734&lngWId=10),
but I would like to find either an example of this in VB .NET or find
an easier way to do it....perhaps built into the ADO provider or .NET
framework base classes.

---

Clarification of Question by indmillert-ga on 02 Feb 2003 09:31 PST

It would be nice if the example program or snippet included a method
of enumerating databases within a selected SQL server too.  Not
necessary for a successful answer, but would be nice.

---

Request for Question Clarification by hammer-ga on 02 Feb 2003 11:26 PST

SQLDMO still appears to be the way to do this. Is there some problem
with using DMO?

- Hammer

---

Clarification of Question by indmillert-ga on 02 Feb 2003 11:52 PST

No problem using DMO under .NET per se, however object architecture
for DMO is radically different for .NET (obviously) and I have been
unable to find any quality detailed documentation on the subject.  And
examples don't exist that I could find.  I also felt that there might
be a better way of doing it under .NET.  Would take a solution using
DMO if it included a VB .NET example of enumerating all SQL servers on
a network...

There is a MSKB article with sample code for how to do this with VB.
How to Enumerate Available SQL Servers Using SQLDMO
http://support.microsoft.com/default.aspx?scid=KB;EN-US;287737

According to the VB.Net newsgroups, this will also work with VB.Net.
Here is a link to one thread which discusses this:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=utf8&threadm=uquwpRyWCHA.1164%40tkmsftngp10&rnum=1&prev=/groups%3Fq%3D%2522VB.Net%2522%2Benumerate%2BSQL%2BServer%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3Dutf8%26selm%3DuquwpRyWCHA.1164%2540tkmsftngp10%26rnum%3D1

The newsgroup thread mentions that you will likely need a patch for
SQLDMO. The necessary Service Packs can be downloaded from Microsoft.
How to Obtain the Latest SQL Server 2000 Service Pack
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q290211

There is another newsgroup posting with a tip for another issue you
may encounter.
"I've used SQLDMO in a VB.NET Beta-1 solution. I had to put "Option
Strict
Off" in the first line of the module where I'm using it. Then I used
late
binding (CreateObject("SQLDMO.Application") and so on). This worked
fine for
me."

Once you have a server, you can use DMO to enumerate the databases by
traversing the Databases collection. If you don't want to use DMO, you
can create and ADO connection to the server and run a query something
like:

SELECT NAME FROM SYSDATABASES 

-----------------------------------------

Additional Resources:
Microsoft SQL-DMO Reference
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_ob_3tlx.asp

Google Search/Google Groups Search terms
SQLDMO enumerate SQL Server VB

Newsgroups:
microsoft.public.dotnet.languages.vb
microsoft.public.sqlserver.programming

----------------------------------

Good luck with your VB.Net project!

- Hammer

---

Request for Answer Clarification by indmillert-ga on 02 Feb 2003 13:52 PST

Thank you for your quick attention and reply!

I do have a further clarification however.  In referencing DMO the
only way that I have found to do so is to add a project reference to
the Microsoft SQL DMO Object Library (sqldmo.dll) under the COM tab of
references.  I don't see anything for SQL DMO under NET.   Wouldn't
you think there should be a "native" .NET equivalent for the DMO
object library?  Reason I ask is because if I use the DMO library --
my project is no longer 100% native .NET code.  Plus I can imagine
that there are probably penalties/issues with having a "hybrid"
COM/.NET application.

If this is the only way to use DMO that I'll go along with it, I just
wonder if there is a .NET equivalent to reference instead of the COM
object.  I don't find any discussion of that in the newsgroups, etc..

Anyway, thank you again for your quick service!!

---

Clarification of Answer by hammer-ga on 03 Feb 2003 05:21 PST

Everything I saw on Microsoft's site used the same SQLDMO library for
both VB6 and .Net. The newsgroups did specifically talk about this in
terms of using COM. I didn't see anyone mentioning any particular ill
effects from doing this. I have found the newsgroup community to be
very helpful and generous with information. If you start a thread
regarding this specific question on one of the newsgroups I mentioned
in my answer, you are likely to get a lot of good and detailed
information.

- Hammer