Get a list of all SQL Servers in a specified Domain


Author: Lothar Haensler

Ever wanted to know which NT machines are used as SQLServers?

When playing around with the NetServerEnum API I found the following solution that I just wanted to share. The code doesn't return the list of servers, it simply does a 'debug.print' - it could be easily modified to return a disconnected ADOR Recordset, an array or even a collection.

Paste the following code into a BAS module :


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
'
'

Download Bas Module (2k)



Comments

  • Crash on NetApiBufferFree

    Posted by Grace on 10/31/2012 07:02am

    For OS Win 7 , when i execute the code, it crashes on NetApiBufferFree. I try to find a solution :(

    Reply
  • Supporting workstations not on a domain

    Posted by Legacy on 10/14/2003 12:00am

    Originally posted by: Raheel A. Khan

    Hi,

    Very handy piece of code. What I am looking for further is:

    How to get the instance of SQL Server 7.0/2000 running on the local machine without a domain, or without a network card for that matter.

    Even though my expertise do not lie in the networking area, I understand that using the NetAPI you have used requires certian netwroking features to be installed.

    Having said that, if there is a solution to this, I assume it would theoretically be possible to run the same code on Win 98.

    Regards,

    Raheel.

    Reply
  • How to do the same with Oracle, Sybase etc..

    Posted by Legacy on 04/01/2003 12:00am

    Originally posted by: Gil Katz

    Hi
    How can i do the same to find out which of the servers in my intranet is Oracle server?
    Thanks
    Gil

    Reply
  • salut

    Posted by Legacy on 04/19/2002 12:00am

    Originally posted by: Maxime cha

    hi I 'm french i' am so happy to be here and the code source is unreally fantastic i love you for the world

    Reply
  • Query

    Posted by Legacy on 11/01/2001 12:00am

    Originally posted by: Vinay Arora

    are you aware of how to use win nt authentication information for vb application , so that if user log in into win nt then vbapplication according to that login provide him different access

    Reply
  • Use "ByVal 0&" for default domain

    Posted by Legacy on 02/07/2001 12:00am

    Originally posted by: Andreas

    Use "ByVal 0&" for default domain
    Don know if this is the proper way. But It works on my win2000 computer.

    l = NetServerEnum(ByVal 0&, _
    level, _
    bufptr, _
    prefmaxlen, _
    entriesread, _
    totalentries, _
    lType, _
    ByVal 0&, _
    hREsume)


    /Andreas

    Reply
  • domain.

    Posted by Legacy on 04/26/2000 12:00am

    Originally posted by: Buu Tran

    Sorry, but...
    How can we get the domain of NT.
    I think this code is more pretty if it can retrieve the active domain of user login?

    Reply
  • Get a list of all SQL Servers in a specified Domain

    Posted by Legacy on 10/28/1999 12:00am

    Originally posted by: Scott Kelley

    This listing only applies to NT. That was not made clear and when I tried my app on a 98 machine, chunks flew everywhere. Let us know if it is not applicable on all platforms.

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • IBM Worklight is a mobile application development platform that lets you extend your business to mobile devices. It is designed to provide an open, comprehensive platform to build, run and manage HTML5, hybrid and native mobile apps.

  • Protecting business operations means shifting the priorities around availability from disaster recovery to business continuity. Enterprises are shifting their focus from recovery from a disaster to preventing the disaster in the first place. With this change in mindset, disaster recovery is no longer the first line of defense; the organizations with a smarter business continuity practice are less impacted when disasters strike. This SmartSelect will provide insight to help guide your enterprise toward better …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds