Getting a list of SQL Servers (VB6 SP5)

Topic

New Reply

Hi,

I’m trying to get a list of SQL servers available without using the SQLDMO.dll as it doesn’t seem to get included with MSDE.

I have an app which may install an instance of MSDE locally, so if I use a method which searches by domain, what will happen on PCs that aren’t connected to a network and dont have a domain?

Any help would be much appreciated.

Reply | Quote

Replies

I found out how to get the users local domain curtesy of Jscher2000

Dim objnetwork
Set objnetwork = CreateObject(“WScript.Network”)
MsgBox “You are ” & objnetwork.UserName & “, using ” & _
objnetwork.ComputerName & “, in domain ” & _
objnetwork.userdomain & “.” & vbCrLf & vbCrLf & “Howdy!”
Set objnetwork = Nothing

The following gets a list of SQL servers in a named domain courtesy of Lothar Haensler from CodeGuru.com:

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

Reply | Quote

Problem solved for now, but just to cover my back, if anyone happens to know what happens if a domain is referenced when no network exists, or software, then i’d be interested to know.

Its been a while since I used Windows 95 without selecting the network options and im not sure if a default domain was provided!

Reply | Quote

I retract that statement – problem not solved after all!
It picks up all of the full versions of SQL, but it doesnt pick up any instances of MSDE!

I have thought of a solution as the server instance should always be called ComputerNameSpirit, but for this method I will need to find how I can detect if the server is running on the local machine. So now im looking for such code, or a fix for the original problem. Any help or tips would be great.

Reply | Quote

We run an application built on top of MSDE, and the code you posted does find it. Could there be a startup switch or other setting missing on your server? (Don’t ask me, though, I didn’t set ours up.) If you try to connect to it from MS Access, is it aware of it?

Reply | Quote

Thanks for your reply jscher2000.
Not entirely sure if I missed anything as I simply installed MSDE with the network option ticked, the SQL login thingy ticked (so you have to provide a password), the SA password and an instance name.

If I use the following code, which needs the dll sqldmo.dll (in the 80toolsbinn directory of SQL) referenced, it picks all of them up:

Dim SQL7 As New SQLDMO.Application
Dim NameList As SQLDMO.NameList
Set NameList = SQL7.ListAvailableSQLServers
Dim x As Integer
For x = 0 To NameList.Count
Me.lstSrvrs.AddItem NameList(x)
‘lstsrvrs is a combo box on a form
Debug.Print NameList(x)
Next

The downside is, that file didnt seem to be included in the MSDE install. Im just about to have a look round the web to see if its a distributably file or not.

Reply | Quote

I must appologise for being an idiot. It was a while ago that I installed MSDE and I forgot that it was installed using the Merge Modules. Not all of the options had been selected on the merge modules, so some of the tools had been left out.
I selected everything, re-built the installer, re-installed MSDE and would you believe it, sqldmo.dll is there! The Latter piece of code now works fine. Still not sure why the initial code didnt pick up all of the servers, but as im on a time limit ill have to come back to that later.

Thanks for your replies, sorry about the MSDE mixup!

Reply | Quote

I must appologise for being an idiot. It was a while ago that I installed MSDE and I forgot that it was installed using the Merge Modules. Not all of the options had been selected on the merge modules, so some of the tools had been left out.
I selected everything, re-built the installer, re-installed MSDE and would you believe it, sqldmo.dll is there! The Latter piece of code now works fine. Still not sure why the initial code didnt pick up all of the servers, but as im on a time limit ill have to come back to that later.

Thanks for your replies, sorry about the MSDE mixup!

Reply | Quote

Thanks for your reply jscher2000.
Not entirely sure if I missed anything as I simply installed MSDE with the network option ticked, the SQL login thingy ticked (so you have to provide a password), the SA password and an instance name.

If I use the following code, which needs the dll sqldmo.dll (in the 80toolsbinn directory of SQL) referenced, it picks all of them up:

Dim SQL7 As New SQLDMO.Application
Dim NameList As SQLDMO.NameList
Set NameList = SQL7.ListAvailableSQLServers
Dim x As Integer
For x = 0 To NameList.Count
Me.lstSrvrs.AddItem NameList(x)
‘lstsrvrs is a combo box on a form
Debug.Print NameList(x)
Next

The downside is, that file didnt seem to be included in the MSDE install. Im just about to have a look round the web to see if its a distributably file or not.

Reply | Quote

We run an application built on top of MSDE, and the code you posted does find it. Could there be a startup switch or other setting missing on your server? (Don’t ask me, though, I didn’t set ours up.) If you try to connect to it from MS Access, is it aware of it?

Reply | Quote

I retract that statement – problem not solved after all!
It picks up all of the full versions of SQL, but it doesnt pick up any instances of MSDE!

I have thought of a solution as the server instance should always be called ComputerNameSpirit, but for this method I will need to find how I can detect if the server is running on the local machine. So now im looking for such code, or a fix for the original problem. Any help or tips would be great.

Reply | Quote

Problem solved for now, but just to cover my back, if anyone happens to know what happens if a domain is referenced when no network exists, or software, then i’d be interested to know.

Its been a while since I used Windows 95 without selecting the network options and im not sure if a default domain was provided!

Reply | Quote

I found out how to get the users local domain curtesy of Jscher2000

Dim objnetwork
Set objnetwork = CreateObject(“WScript.Network”)
MsgBox “You are ” & objnetwork.UserName & “, using ” & _
objnetwork.ComputerName & “, in domain ” & _
objnetwork.userdomain & “.” & vbCrLf & vbCrLf & “Howdy!”
Set objnetwork = Nothing

The following gets a list of SQL servers in a named domain courtesy of Lothar Haensler from CodeGuru.com:

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

Reply | Quote