RE: exchange qn
From: vishal subramaniam (vishalsu_at_microsoft.com)
Date: 05/03/04
- Next message: Bojidar Alexandrov: "Re: Duplicate sql"
- Previous message: Bojidar Alexandrov: "Re: Duplicate sql"
- In reply to: BF: "exchange qn"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 03 May 2004 06:56:49 GMT
vishalsu@online.microsoft.com
ISSUE:
======
I have a load of contact data in SQL Server. Is there any way I can expose
this data in Exchange?
RESOLUTION/LINKS;
=====================
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
LINKS:
======
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8
_qd_12_0t5x.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmes2k/htm
l/buildingsolutions.asp
This posting is provided "AS IS" with no warranties, and confers no rights.
- Next message: Bojidar Alexandrov: "Re: Duplicate sql"
- Previous message: Bojidar Alexandrov: "Re: Duplicate sql"
- In reply to: BF: "exchange qn"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
