Post reply

Using active directory to locate SQL servers

  • January 1, 2006 at 6:14 pm

    #96244

    I'm finding the code I currently use that uses both SQLBrowseConnect and SQL-DMO does not find installs of SQL Express 2005 even if the server is running.  In the cases where the sql server was not found, the sql server had been added to ActiveDirectory. 

    Can anyone show me the proper LDAP query or query format to locate all sql servers in the enterprise?  I was planning on using ADSI ( oledb) to run the query.

    Thanks,

    Jim Kane

  • January 3, 2006 at 9:11 am

    #612420

    You will need to changed the canoical name and DC in the LDAP query, but the following vbscript retrieves instance names for SQL Servers computers registered in AD

    Const ADS_SCOPE_SUBTREE = 2

    Set objConnection = CreateObject("ADODB.Connection")

    Set objCommand =  CreateObject("ADODB.Command")

    objConnection.Provider = "ADsDSOObject"

    objConnection.Open "Active Directory Provider"

    Set objCommand.ActiveConnection = objConnection

    objCommand.CommandText = _

        "Select displayName from 'LDAP://CN=Computers,DC=mydomain,DC=com' " _

            & "where objectClass='mS-SQL-SQLServer'"

    objCommand.Properties("Page Size") = 1000

    objCommand.Properties("Timeout") = 10

    objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE

    objCommand.Properties("Cache Results") = False

    Set objRecordSet = objCommand.Execute

    objRecordSet.MoveFirst

    Do Until objRecordSet.EOF

        Wscript.Echo "Computer Name: " & objRecordSet.Fields("displayName").Value

        objRecordSet.MoveNext

    Loop

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply