Delen via

  • Artikel

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 similar to the SQL subset supported by the OLE DB Provider for Microsoft Indexing Service.

Microsoft SQL Server distributed queries can be used to query data from the Exchange Web Store through this OLE DB Provider and can be joined to 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 by 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 authenticated logins, there are no login mappings required. The logins are impersonated by SQL Server when it connects to the OLE DB Provider for Exchange. For SQL Server authenticated logins, set up login mappings by supplying the user name and password, as required.

The following restrictions apply 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. To perform comparisons against these columns, the columns must be converted explicitly to nvarchar by using the CONVERT function.

  • Multivalued 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 as previously established

  • 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 named Contacts that contains a list of contacts. The following script creates a view against the Contacts folder and retrieves the first name, last name, company name, and date of birth of each contact.

    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 ''.\contacts''')

    Now the views can be queried and joined to local SQL Server tables like regular tables. For example, the Contacts view can be joined to 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 about the SQL language supported by the Exchange OLE DB provider, see the Microsoft Exchange 2000 documentation.