Exchange Store - FAQs # 2

Here are some questions that are frequently asked by my customers whenever they try to access/enumerate Exchange Store using SQL:

1 ) In Exchange Store, how to get all properties for a particular item?  

To get all the properties for a particular item, bind to it directly by using various technologies like Activex Data Objects, Collaboration Data Objects, OLE DB, or through WebDAV.

2) So, can i get all properties for all items using SELECT * ?

This is one of the frequently asked questions from our customers whenever they try to access any of the Exchange Store. But we can't get all properties for all items. Because each item in the folder could have different properties in it.

3) So, what will we get when we make use of SELECT * ?

When running the SELECT * statement on a folder, we can get only the list of properties. These properties are defined by the schema for the folder.

4) What're the properties that cannot be searched?  

The following properties cannot be searched because their values are only calculated when used and are not stored in the Exchange store.

DAV:getetag, DAV:href, DAV:lockdiscovery, DAV:parentname, DAV:resourcetype, DAV:searchrequest
https://schemas.microsoft.com/exchange/content-href
https://schemas.microsoft.com/exchange/ntsecuritydescriptor
https://schemas.microsoft.com/exchange/oof-state
https://schemas.microsoft.com/exchange/publicfolderemailaddress
https://schemas.microsoft.com/repl/repl-uid
https://schemas.microsoft.com/repl/resourcetag
urn:schemas:contacts:proxyaddresses
urn:schemas:httpmail:htmldescription
urn:schemas:httpmail:subject
urn:schemas:mailheader:subject
urn:schemas-microsoft-com:exch-data:baseschema
urn:schemas-microsoft-com:exch-data:schema-collection-ref

All properties that contain URL values, such as DAV:href, are calculated and UTF-8 encoded. Even this list is not comprehensive and is subject to change.

5) What're the SQL grammar, functions and functionality are not supported?

AVG, CONVERT (use CAST), COUNT, CREATE VIEW, DATASOURCE, DELETE, DROP INDEX, INSERT, JOINS, MAX, MIN, Revision ID header (ignored), Scope aliases, SELECT DISTINCT, SET, Scope revision numbers, SHAPE, SUM, UNKNOWN, UPDATE, Wildcard expressions (use CONTAINS, FREETEXT, and LIKE)

Note: This list is not comprehensive and is subject to change.

6) When i try to search or index certain properties, it throws errors or getting no results?

In Exchange store calculates some of the properties but does not store those values/entires. But some of these properties are cached, however, and can be searched or indexed.

7) What is the property needs to set to access hidden items from MAPI Clients?

To hide items that are accessible to MAPI clients (like Outlook), you must set the https://schemas.microsoft.com/mapi/proptag/x67aa000b property to true when the item is created.

Note: If the flag is not set when the item is first created, you cannot hide it later from MAPI clients.