Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Getting a list of SQL Servers (VB6 SP5)

    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.

  2. #2
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting a list of SQL Servers (VB6 SP5)

    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

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting a list of SQL Servers (VB6 SP5)

    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!

  4. #4
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting a list of SQL Servers (VB6 SP5)

    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.

  5. #5
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Getting a list of SQL Servers (VB6 SP5)

    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?

  6. #6
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting a list of SQL Servers (VB6 SP5)

    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.

  7. #7
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting a list of SQL Servers (VB6 SP5)

    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •