ADO can also use SQL syntax to query Active Directory. This syntax is more familiar to some people, but many SQL features are not supported. SQL syntax uses the keywords SELECT and FROM. You can also use the keywords WHERE and even ORDER BY.

Both LDAP and SQL queries are strings that are assigned to properties of ADO objects. Like any VBScript string, the value is enclosed with double quotes. If you are using LDAP syntax, embedded string values in the query are not enclosed by quotes. For example, the following LDAP syntax query uses several string values, like "person" and "user":

"<LDAP://ou=Sales,dc=MyDomain,dc=com>;" _
     & "(&(objectCategory=person)(objectClass=user));" _
     & "sAMAccountName,cn;Subtree"

SQL syntax requires that embedded strings be enclosed with single quotes. For example, the same query in SQL syntax would be:

"SELECT sAMAccountName,cn " _
     & "FROM 'LDAP://ou=Sales,dc=MyDomain,dc=com' " _
     & "WHERE objectCategory='person' AND objectClass='user'"

Date values are also enclosed in single quotes, but numeric values are not. Note also that you must be careful to include spaces in the correct locations. If any string values in an SQL statement have single quote characters, the single quotes must be doubled. For example:

"SELECT distinguishedName, displayName " _
     & "FROM 'LDAP://ou=Sales,dc=MyDomain,dc=com' " _
     & "WHERE sAMAccountName='maryo''brian'"

Some more examples of SQL syntax queries follow:

To return sAMAccountName and distinguishedName of all objects of class "person" in the Sales Organizational Unit that are not members of any group (except their "primary" group):

"SELECT sAMAccountName, distinguishedName " _
     & "FROM 'LDAP://ou=Sales,dc=MyDomain,dc=com' " _
     & "WHERE objectClass='person' AND NOT memberOf='*'"

To return sAMAccountName and distinguishedName of all users objects that do not expire in ou=Sales:

"SELECT sAMAccountName, distinguishedName " _
     & "FROM 'LDAP://ou=Sales,dc=MyDomain,dc=com' " _
     & "WHERE objectCategory='person' " _
     & "AND objectClass='user' " _
     & "AND (accountExpires=0 OR accountExpires=9223372036854775807)"

To return sAMAccountName of all objects created after September 2, 2005, in ou=Sales:

"SELECT sAMAccountName " _
     & "FROM 'LDAP://ou=Sales,dc=MyDomain,dc=com' " _
     & "WHERE createTimestamp>='20050902000000.0Z'"

If you specify SELECT * the only attribute value returned is the ADsPath. For example, the following returns the ADsPath of all groups in ou=Sales:

"SELECT * FROM 'LDAP://ou=Sales,dc=MyDomain,dc=com' " _
     & "WHERE objectClass='group'"

To return distinguishedName of all user objects in ou=Sales with a value of 546 assigned to the userAccountControl attribute:

"SELECT distinguishedName " _
     & "FROM 'LDAP://ou=Sales,dc=MyDomain,dc=com' " _
     & "WHERE objectCategory='person' AND objectClass='user' " _
     & "AND userAccountControl=546"

To return distinguishedName of object in ou=Sales with GUID = "6394351061438F4B82662379F7C4408E":

"SELECT distinguishedName " _
     & "FROM 'LDAP://ou=Sales,dc=MyDomain,dc=com' " _
     & "WHERE objectGuid='\63\94\35\10\61\43\8F\4B\82\66\23\79\F7\C4\40\8E'"

To return sAMAccountName of all objects in ou=Sales that are members of group TestGroup:

"SELECT sAMAccountName " _
     & "FROM 'LDAP://ou=Sales,dc=MyDomain,dc=com' " _
     & "WHERE memberOf='cn=TestGroup,ou=West,dc=MyDomain,dc=com'"

To return distinguishedName and whenCreated for all users in ou=Sales created after September 1, 2006:

"SELECT distinguishedName, whenCreated " _
     & "FROM 'LDAP://ou=Sales,dc=MyDomain,dc=com' " _
     & "WHERE objectCategory='person' AND objectClass='user' " _
     & "AND whenCreated>='20060901000000.0Z'"

To return distinguishedName of all users in ou=Sales that have no value assigned to the description attribute:

"SELECT distinguishedName " _
     & "FROM 'LDAP://ou=Sales,dc=MyDomain,dc=com' " _
     & "WHERE objectCategory='person' AND objectClass='user' " _
     & "AND NOT description ='*'"

To return the values of the distinguishedName, cn, sn, and givenName attributes of all user objects in ou=Sales sorted by cn:

"SELECT distinguishedName, cn, sn, GivenName " _
     & "FROM 'LDAP://ou=Sales,dc=MyDomain,dc=com' " _
     & "WHERE ObjectCategory='person' And ObjectClass='user' " _
     & "ORDER By cn"

An example using range limits would be:

"SELECT 'member;range=0-999', sAMAccountName " _
     & "FROM 'LDAP://ou=Sales,dc=MyDomain,dc=com' " _
     & "WHERE ObjectCategory='group'"

Note in the previous example that the attribute name and range limits are enclosed by single quotes. There is no way known to test bits of the userAccountControl attribute using SQL syntax.

If the version of MDAC on the client is less than 2.8, the maximum number of attribute values that can be retrieved using SQL syntax is 49. For example, a Windows 2000 Professional computer with MDAC 2.7 will raise an error if you attempt to retrieve more than 49 attributes using SQL syntax. No such limit has been seen if MDAC is 2.8 or greater. Also, no limit on any client OS has been experienced using LDAP syntax. The version of MDAC on a client can be determined from the version of the files msdadc.dll and oledb32.dll.