Go Back  Xtreme Visual Basic Talk > Legacy Visual Basic (VB 4/5/6) > API > Getting SQL Server Instance List with NetServerEnum


Reply
 
Thread Tools Display Modes
  #1  
Old 09-16-2003, 09:44 AM
reptileqc reptileqc is offline
Newcomer
 
Join Date: Sep 2003
Location: Montreal, QC
Posts: 11
Exclamation Getting SQL Server Instance List with NetServerEnum


Hi everyone,

I've been doing research yesterday for several hours without finding what I was really looking for.

I need to get a list of SQL Server available on the network with their instance name. This is simply to populate a combo box in Access to let the user choose their server location.

I saw a couple answers on this board regarding this question, and most of the answers were to use the "SQLDMO" object and its references. But I don't want to do that because that would imply deploying those librairies to all computers just to ease up on the programming.

I know there is a way to list the SQL Servers from the NetServerEnum but the problem is that the instances names are missing from that list.

If you go through the ODBC Data Sources in windows, Windows is able to get a list of all the SQL servers and instances names, so why can't we?

I found a function called SQLBrowseConnect() that I guess is from the ODBC32.dll but I can't get any good example for VBA and how to use it.

Let me know your thoughts.

Thanks a lot.
Reply With Quote
  #2  
Old 09-16-2003, 12:10 PM
UDA_Brad UDA_Brad is offline
Newcomer
 
Join Date: Aug 2003
Location: WAR EAGLE!
Posts: 15
Lightbulb

Hi,

The Extreme Visual Basic Forum is having troubles right now taking posts, but here is some code that may get you started.

----------
Code:
Option Explicit Private Declare Function lstrlenW Lib "kernel32" (ByVal lpString As Long) As Long Private Declare Function NetServerEnum Lib "netapi32" ( _ strServername As Any, _ ByVal level As Long, _ bufptr As Long, _ ByVal prefmaxlen As Long, _ entriesread As Long, _ totalentries As Long, _ ByVal servertype As Long, _ strDomain As Any, _ resumehandle As Long) As Long Private Declare Function NetApiBufferFree Lib "Netapi32.dll" (ByVal lpBuffer As Long) As Long Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long) Private Const SV_TYPE_SERVER As Long = &H2 Private Const SV_TYPE_SQLSERVER As Long = &H4 Private Type SV_100 platform As Long name As Long End Type Public Sub GetSQLServers() ' ' You could change this to be a function returning ' a list of the SQL servers in a ADOR Recordset or an array etc. ' ' At present, it just does a debug.print of all the ' SQL servers on the network. ' ' Dim l As Long Dim entriesread As Long Dim totalentries As Long Dim hREsume As Long Dim bufptr As Long Dim level As Long Dim prefmaxlen As Long Dim lType As Long Dim domain() As Byte Dim i As Long Dim sv100 As SV_100 level = 100 prefmaxlen = -1 lType = SV_TYPE_SQLSERVER domain = "placeYourDomainNameHere" & vbNullChar l = NetServerEnum(ByVal 0&, _ level, _ bufptr, _ prefmaxlen, _ entriesread, _ totalentries, _ lType, _ domain(0), _ hREsume) If l = 0 Or l = 234& Then For i = 0 To entriesread - 1 CopyMemory sv100, ByVal bufptr, Len(sv100) Debug.Print Pointer2stringw(sv100.name) bufptr = bufptr + Len(sv100) Next i End If NetApiBufferFree bufptr ' End Sub Private Function Pointer2stringw(ByVal l As Long) As String Dim buffer() As Byte Dim nLen As Long ' nLen = lstrlenW(l) * 2 If nLen Then ReDim buffer(0 To (nLen - 1)) As Byte CopyMemory buffer(0), ByVal l, nLen Pointer2stringw = buffer End If End Function
-------------

Good Luck - Brad

Quote:
Originally Posted by reptileqc
Hi everyone,

I've been doing research yesterday for several hours without finding what I was really looking for.

I need to get a list of SQL Server available on the network with their instance name. This is simply to populate a combo box in Access to let the user choose their server location.

I saw a couple answers on this board regarding this question, and most of the answers were to use the "SQLDMO" object and its references. But I don't want to do that because that would imply deploying those librairies to all computers just to ease up on the programming.

I know there is a way to list the SQL Servers from the NetServerEnum but the problem is that the instances names are missing from that list.

If you go through the ODBC Data Sources in windows, Windows is able to get a list of all the SQL servers and instances names, so why can't we?

I found a function called SQLBrowseConnect() that I guess is from the ODBC32.dll but I can't get any good example for VBA and how to use it.

Let me know your thoughts.

Thanks a lot.

Reply With Quote
  #3  
Old 09-16-2003, 03:09 PM
00100b's Avatar
00100b 00100b is offline
Martian In Disguise

Retired Moderator
* Guru *
 
Join Date: May 2003
Location: Minneapolis, MN
Posts: 9,566
Default

Here's a little less code, but the inclusion of an additional reference.
Code:
Private Sub Command1_Click() ' Add a reference to the Microsoft SQLDMO Object Library Dim objSQLApp As SQLDMO.Application Dim lngIndex As Long Set objSQLApp = New SQLDMO.Application With objSQLApp.ListAvailableSQLServers For lngIndex = 1 To .Count Combo1.AddItem .Item(lngIndex) Next lngIndex End With End Sub
__________________
The only stupid question is the one that goes un-asked.
Reply With Quote
  #4  
Old 09-16-2003, 03:49 PM
reptileqc reptileqc is offline
Newcomer
 
Join Date: Sep 2003
Location: Montreal, QC
Posts: 11
Unhappy Working on it...

Thanks 00100b,

but I already had this one... like I was saying in my original post, I don't want to be using the SQLDMO references because that would require a deploy of the SQL files which is uncessary in my opinion.

-------

Thanks UDA_Brad,

It seems like we can post once again.... it was a rough day for the board I guess.

I got your code sample, thanks a lot but that's the one I got already. It's working well to find the actual server names that run SQL Server on the network. But if a SQL Server uses an instance name that is different than the default one, I won't be able to connect to it.

For example :

On my network, I have two SQL Servers :
1. ALEX is running on the default instance and is accessible through ALEX

2. MAINSQL is running on a specific instance called DESKTOP and is accessible through MAINSQL\DESKTOP

If I use the code you gave me to detect the available servers, I'm only able to retrieve the list of the SERVER NAMES but there is no way to tell if it's using an instance name on top of it.

For example, i'm going to get the list that returns :
ALEX
MAINSQL

I can connect to ALEX but I can't connect to MAINSQL because the instance name is not specified and is required.

That's where my problem is so far.

Thanks for you help!
Reply With Quote
  #5  
Old 09-16-2003, 03:51 PM
00100b's Avatar
00100b 00100b is offline
Martian In Disguise

Retired Moderator
* Guru *
 
Join Date: May 2003
Location: Minneapolis, MN
Posts: 9,566
Default

That will teach me to read more carefully.

Sorry.
__________________
The only stupid question is the one that goes un-asked.
Reply With Quote
  #6  
Old 09-30-2003, 08:32 AM
ManWithoutName ManWithoutName is offline
Newcomer
 
Join Date: Sep 2003
Posts: 2
Unhappy

Hello reptileqc,

I have the same problem as your problem. When I use NetServerEnum, it only list SQL Server default instance name, but when I have a different instance than the default, for example my_server_name\my_sql_instance_name, I only get my_server_name

Did you or anyone find a solution to such that problem.

P.S: I do not want to use SQL-DMO


Thanks in advance
Reply With Quote
  #7  
Old 10-01-2003, 09:07 AM
reptileqc reptileqc is offline
Newcomer
 
Join Date: Sep 2003
Location: Montreal, QC
Posts: 11
Default Solution to our problem

Hi guys,

I found a solution and a way to do with just like I expected it without using SQL-DMO.

I will post another message with the code later on today when I have some free time.

Later
Reply With Quote
  #8  
Old 10-02-2003, 01:35 AM
ManWithoutName ManWithoutName is offline
Newcomer
 
Join Date: Sep 2003
Posts: 2
Talking RE: Getting SQL Server Instance List with NetServerEnum

Hi reptileqc,

You make me happy by your last reply, please when you have time, do not forget me with that solution.

Thanks again.
Reply With Quote
  #9  
Old 10-07-2003, 03:00 PM
chaulmoogra chaulmoogra is offline
Newcomer
 
Join Date: Oct 2003
Posts: 1
Default

Hello. I'm working on this exact problem and would love to know the solution you found. Could you post a sample, or at least a hint?

Thanks.
Reply With Quote
  #10  
Old 10-07-2003, 03:36 PM
karlt karlt is offline
Newcomer
 
Join Date: Oct 2003
Location: Toronto, ON
Posts: 2
Default

Hi reptileqc

Please add me to the list of those who'd love to see your solution - have the identical problem/requirement :-)

Thanks
Reply With Quote
  #11  
Old 10-07-2003, 04:43 PM
reptileqc reptileqc is offline
Newcomer
 
Join Date: Sep 2003
Location: Montreal, QC
Posts: 11
Lightbulb Solution! (Finally!)

Sorry guys to had you waiting for so long, I had to actually work on the function myself to make it simpler and easy to understand.

So here it is :

Code:
Public Const SQL_HANDLE_DBC = 2 Public Declare Function SQLAllocEnv Lib "odbc32.dll" (phenv As Long) As Integer Public Declare Function SQLAllocHandle Lib "odbc32.dll" (ByVal hType As Integer, _ ByVal hInput As Long, _ ByRef phOutput As Long) As Integer Public Declare Function SQLBrowseConnect Lib "odbc32.dll" (ByVal hDbc As Long, _ ByVal szConnStrIn As String, _ ByVal cbConnStrIn As Integer, _ ByVal szConnStrOut As String, _ ByVal cbConnStrOutMax As Integer, _ pcbconnstrout As Integer) As Integer Public Declare Function SQLDisconnect Lib "odbc32.dll" (ByVal hDbc As Long) As Integer Public Declare Function SQLFreeConnect Lib "odbc32.dll" (ByVal hDbc As Long) As Integer Public Declare Function SQLFreeEnv Lib "odbc32.dll" (ByVal hEnv As Long) As Integer Public Function GetSQLServers() As String Dim retCode As Long Dim hDbc As Long Dim hEnv As Long Dim strCon As String Dim strOutCon As String Dim intConLenOut As Integer strCon = "DRIVER={SQL Server};" strOutCon = Space(1000) retCode = SQLAllocEnv(hEnv) retCode = SQLAllocHandle(SQL_HANDLE_DBC, ByVal hEnv, hDbc) retCode = SQLBrowseConnect(ByVal hDbc, strCon, Len(strCon), strOutCon, Len(strOutCon) + 2, intConLenOut) strOutCon = Left(strOutCon, intConLenOut) strOutCon = Mid(strOutCon, InStr(1, strOutCon, "Server={") + 8, (InStr(1, strOutCon, "}") - (InStr(1, strOutCon, "Server={") + 8))) GetSQLServers = strOutCon retCode = SQLDisconnect(hDbc) retCode = SQLFreeConnect(hDbc) retCode = SQLFreeEnv(hEnv) End Function

A co-worker gave it to me and I simplified it so that it now gives you the result in a string instead of an array. The only thing left is to replace the "," by ";" and it can be inserted easily into drop down lists!

He also told me that the way the SQLAllocHandle is working in this example is actually an old way of doing it. Microsoft is suggesting new ways to get the Handles but he couldn't figure it out and I didn't try it either. So you may end up having to make slight changes to this code for future compatibility but for now this is working fine.

Hope this helps!

Thanks for you help anyway.

Last edited by reptileqc; 10-07-2003 at 04:49 PM.
Reply With Quote
  #12  
Old 10-09-2003, 04:09 PM
karlt karlt is offline
Newcomer
 
Join Date: Oct 2003
Location: Toronto, ON
Posts: 2
Default

Thanks reptileqc!!!

Works like a charm. Just in case someone isn't completely familiar with the SQLBrowseConnect string that gets returned for the list - all that's needed is:

Dim i as integer
Dim x as string
Dim y

x = GetSQLServers
y = split(x, ",")
for i = 0 to ubound(y)
....... do whatever you want
next
Reply With Quote
  #13  
Old 10-10-2003, 07:22 AM
reptileqc reptileqc is offline
Newcomer
 
Join Date: Sep 2003
Location: Montreal, QC
Posts: 11
Thumbs down Possible Problems

I'm glad it's finally useful to someone.

BTW, one of the possible problems with the piece of code I submitted earlier is the 1000 characters limit assigned to my "strOutCon". You may need to increase the length of the string if you have a lot of servers on your local network or if you are actualling running this code directly from the Microsoft offices. :P

Most APIs I worked with before were returning a pointer and a length so that I can actually resize my local variable before copying the right amount of data from the pointer to my variable. This does not seem to be the case here. What's the most efficient way to work in this case?

I guess I made it easy but there must be a better way.

Thanks.
Reply With Quote
  #14  
Old 10-15-2003, 03:46 AM
vrbhatt vrbhatt is offline
Newcomer
 
Join Date: Oct 2003
Posts: 1
Unhappy Sorry did not work

Hi.
Sorry. Did not work. Do U have any idea about it's pre-requisites.
Using VB.NET

retcode value is "83755006" but strOutCon=""
Thanks for help.
Reply With Quote
  #15  
Old 10-15-2003, 08:39 AM
reptileqc reptileqc is offline
Newcomer
 
Join Date: Sep 2003
Location: Montreal, QC
Posts: 11
Default Prerequisites

Hi there,

Well in terms of prerequisite... make sure you've got pretty much all the code I supplied earlier in this thread.

The two other functions are required as well as the string allocation for the returned value.

If you copy paste the whole code into a window it should work for you.

The returned value you are giving me is not one of those that are actually possible, the possible results should be :

SQL_SUCCESS = 0
SQL_SUCCESS_WITH_INFO = 1
SQL_NEED_DATA = 99
SQL_ERROR = -1
SQL_INVALID_HANDLE = -2

I wasn't able (even when changing data types or omitting values) to get a different result. In your case that might be an environnement error.

Also I heard that the SQLAllocEnv and SQLAllocHandle might work differently in .NET

I'll look around for solutions... so keep me posted if you find one before I do.
Reply With Quote
  #16  
Old 10-24-2003, 02:03 PM
flindian flindian is offline
Newcomer
 
Join Date: Oct 2003
Posts: 1
Smile

Hi all,

I have been trying to do the same thing in VB .NET and could not get NetServerEnum to display SQL Instances or SQLBrowseConnect to work at all.

There IS another way though using managed VB .NET code:

Read the registry key:
HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL
Server\80\Tools\SQLEW\Registered Servers X
using the .NET frameworks Microsoft.win32.Registry classes.

Much easier!

flindian
Reply With Quote
  #17  
Old 10-24-2003, 02:08 PM
00100b's Avatar
00100b 00100b is offline
Martian In Disguise

Retired Moderator
* Guru *
 
Join Date: May 2003
Location: Minneapolis, MN
Posts: 9,566
Default

[ednf]sorry[/ednf]
__________________
The only stupid question is the one that goes un-asked.
Reply With Quote
  #18  
Old 10-24-2003, 03:15 PM
reptileqc reptileqc is offline
Newcomer
 
Join Date: Sep 2003
Location: Montreal, QC
Posts: 11
Default using APIs

Main rule of APIs programming... is never to read the registry entries directly.

This location might change in future versions of the operating system (read Windows here) while using the API should keep you from having to reprogram a new version everytime there is a change in the registry.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to Get SQL Server List / Database List kemblecai Database and Reporting 2 11-11-2002 06:55 AM
Problem with writing Twice Renidrag Web Programming 20 11-07-2002 03:52 PM
Creating Access Databases TomGuy Database and Reporting 8 07-10-2002 11:56 AM
Q's re. usage of SQL Server Mike Database and Reporting 6 07-02-2002 08:44 PM

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
 
 
-->