OLE DB Provider for Exchange

The Microsoft® OLE DB Provider for Exchange exposes data stored in a Microsoft Exchange 2000 Web Store in tabular form. This data can be queried using an SQL-like language that is very similar to the SQL subset supported by the OLE DB Provider for Microsoft Indexing Service.

Microsoft SQL Server™ 2000 distributed queries can be used to query data from the Exchange Web Store through this OLE DB Provider and can be joined with tables in SQL Server. The Exchange Web Store should be located in the same computer as SQL Server. Web Stores located in other computers cannot be accessed using the OLE DB Provider for Exchange.

The OLE DB Provider for Exchange is available as part of Microsoft Exchange 2000.

To create a linked server against an Exchange Web Store

• Use 'exoledb.datasource.1' as the provider_name argument, and the URL corresponding to the root folder of the Web Store as the data_source argument of the sp_addlinkedserver system stored procedure.

  EXEC sp_addlinkedserver 'exchange', 
  'Exchange OLE DB provider', 
  'exoledb.DataSource.1',
  'file:\\.\backofficestorage\localhost\public folders'
  

• For Windows NT Authenticated logins, there are no login mappings necessary. They are impersonated by SQL Server when it connects to the OLE DB Provider for Exchange. For SQL Authenticated logins, set up login mappings by supplying the user name and password, as necessary.

The following restrictions are applicable when querying data from the OLE DB Provider for Exchange:

• Only pass-through queries are supported. Four-part names cannot be used against the Exchange OLE DB provider.
  
  
• All character columns from the OLE DB Provider for Exchange are exposed to SQL Server as ntext columns. In order to perform comparisons against these columns, they have to be converted explicitly to nvarchar using the CONVERT function.
  
  
• Multi-valued columns from the Exchange provider with OLE DB DBTYPE DBTYPE_VECTOR are not supported from SQL Server Distributed Queries.

To access data in the Exchange Web Store from SQL Server, through a linked server established as above

• Create views that retrieve the required properties as columns from the Web Store folder of interest. The view definition converts string columns to nvarchar so that they can be filtered through conditions in the WHERE clause.

  For example, let the Web Store contain a folder called Contacts that contains a list of contacts. The following script creates a view against the Contacts folder while retrieving the Contact's first name, last name, company name, and date of birth.

  CREATE VIEW Contacts
  AS
  SELECT convert(nvarchar(30),"urn:schemas:contacts:sn") LastName,
  Convert(nvarchar(30),"urn:schemas:contacts:givenname") 
  FirstName,
      Convert(nvarchar(30), "urn:schemas:contacts:o") Company,
      Convert(nvarchar(50), "urn:schemas:contacts:email1") Email,
      "urn:schemas:contacts:bday" BirthDay
  FROM OpenQuery(Exchange, 
    'SELECT "urn:schemas:contact:sn", 
             "urn:schema:contacts:givenname", 
            "urn:schemas:contacts:o",
          "urn:schemas:contacts:email1"
          "urn:schemas:contacts:bday"
     FROM SCOPE(''.\contacts'')'
  

  Now the views can be queried and joined with local SQL Server tables like regular tables. For example, the Contacts view can be joined with a local Suppliers table to determine Contact information for the list of Supplier companies.

  SELECT FirstName, LastName, Email, Company
  FROM    Suppliers S, Contacts C
  WHERE S.Company = C.CompanyName
  

For information on the SQL language supported by the Exchange OLE DB provider see Microsoft Exchange 2000 documentation.