Searching by Using the SELECT Statement

Topic Last Modified: 2006-11-06

Important

Use Exchange Web Services to search for items in the Exchange database. For more information see Web Services.

You can use a SELECT Statement to return the values of properties for items in a particular folder, or folders, within a store. Each SELECT Statement is executed within the context of an OLE DB session (Microsoft® ActiveX® Data Objects (ADO) connection) that implicitly defines the public store or mailbox store and the folder tree being searched. The SELECT Statement has the following syntax:

SELECT select-list | *
FROM SCOPE(resource-list)
[WHERE search-condition]
[order-by-clause]

To request specific properties, you enclose each property name in quotation marks and separate each property name with a comma. For example:

SELECT "DAV:href", "DAV:displayname" ...

Note that calculated properties are Non-Searchable Properties and should not be used in the SELECT Statement.

The Search Scope to execute is the combination of a folder URL and a traversal depth. For example:

'shallow traversal of "https://server/vroot/folder1"'
'shallow traversal of "file://./backofficestorage/domain.tld/pub2/f1"'

When you execute a Structured Query Language (SQL) SELECT Statement using the Exchange OLE DB (ExOLEDB) provider, you can use a URL relative to the top-level public folder. A relative URL can be used as well, as in the following example:

'shallow traversal of "/folder1/"'
'deep traversal of "/folder1/folder2/"'

A FROM Predicate does not require an explicit SCOPE Element, in which case a shallow traversal is assumed. For example:

FROM "https://server/vroot/folder1"

If a SCOPE Element is specified in the SQL query statement but a depth is not, the Exchange store query processor assumes a deep traversal, as in the following example:

FROM SCOPE("https://server/vroot/folder1") (deep traversal here)

Tokens, which are properties in a select list or values in a WHERE Clause, must be delimited by single quotations marks. A token can contain only alphabetic characters unless it is also enclosed in double quotation marks. In the following SQL WHERE Clause, a full-text search is requested. In the following request string, "this" and "that" are tokens:

WHERE CONTAINS (' "this" AND "that" ')

This clause does not work:

WHERE CONTAINS ('10 dollars')

To fix the clause, surround the token with double quotation marks within the single quotation marks:

WHERE CONTAINS ('"10 dollars"')

Use of the CONTAINS Predicate is restricted to properties that are marked for full-text indexing, and only if full-text searching has been enabled for that particular store.

The Exchange store does not support deep traversals in the public store designated for MAPI clients, such as Microsoft Outlook®. If either an explicit or implicit deep traversal is requested, the Exchange store query processor returns an error.