Directory Services

SQL Dialect

The SQL dialect, derived from the Structured Query Language, uses human-readable expressions to define query statements. Use a SQL query statement with the following ADSI search interfaces:

The ActiveX Data Object (ADO) interfaces, which are OLE Automation interfaces that utilize OLE DB.
OLE DB, which is a set of C/C++ interfaces for querying databases.

SQL statements require the following syntax.

SELECT [ALL] * | select-list FROM 'ADsPath' [WHERE search-condition] [ORDER BY sort-list]

The following table lists SQL query statement keywords.

Keyword	Description
SELECT	Specifies a comma-separated list of attributes to retrieve for each object. If you specify *, the query retrieves only the ADsPath of each object.
FROM	Specifies the ADsPath of the base of the search. For example, the ADsPath of the Users container in an Active Directory domain might be 'LDAP://CN=Users,DC=Fabrikam,DC=COM'. Be aware that the path is enclosed in a pair of single quotation marks (').
WHERE	An optional keyword that specifies the query filter.
ORDER BY	An optional keyword that generates a server-side sort if the server supports the LDAP sort control. Active Directory supports the sort control, but it can impact server performance, particularly if the results set is large. The sort-list is a comma-separated list of attributes on which to sort. Be aware that Active Directory supports only a single sort key. You can use the optional ASC and DESC keywords to specify ascending or descending sort order; the default is ascending. The ORDER BY keyword overrides any sort key specified with the "Sort on" property of the ADO Command object.

The following query statements are examples of SQL dialect in ADSI.

To search for all the group objects.

SELECT ADsPath, cn FROM 'LDAP://DC=Fabrikam,DC=COM' WHERE objectCategory='group'

To search for all the users whose Last Name starts with letter H.

SELECT ADsPath, cn FROM 'LDAP://OU=Sales,DC=Fabrikam,DC=COM' WHERE objectCategory='person' AND objectClass='user' AND sn = 'H*' ORDER BY sn

The formal grammar for SQL queries is defined in the following code example. All keywords are case-insensitive.

statement ::= select-statement
select-statement ::= SELECT [ALL] select-list FROM table-identifier [WHERE search-condition] [ORDER BY sort-list]
select-list ::= * | select-sublist [, select-sublist]... 
select-sublist ::= column-identifier
column-identifier ::= user-defined-name 
table-identifier ::= string-literal
search-condition ::= boolean-term [OR search-condition]
sort-list ::= column-identifier [ASC | DESC] [,column-identifier [ASC | DESC]]... 
boolean-term ::= boolean-factor [AND boolean-term]
boolean-factor ::= [NOT] boolean-primary
boolean-primary ::= comparison-predicate | (search-condition)
comparison-predicate ::= column-identifier comparison-operator literal
comparison-operator ::= < | > | <= | >= | = | <>
user-defined-name ::= letter [letter | digit]...
literal ::= string-literal | numeric-literal | boolean-literal 
string-literal ::= '{character}...' (Any sequence of characters delimited by quotes)
numeric-literal ::= digits [fraction] [exponent]
digits ::= digit [digit]...
fraction ::= . digits 
exponent ::= E digits
boolean-literal ::= TRUE | FALSE | YES | NO | ON | OFF

SQL inner joins are not supported by the Active Directory OLE DB provider, but you can use SQL to join SQL and Active Directory data. For more information, see Creating a Heterogeneous Join between SQL Server and Active Directory.