So here's my dilemma. I need an automated SQL Server (2000) job that
runs once once per hour.

This jobs should:

1) Retrieve unread email for the user "Le" from our Exchange Server,
and insert it into a table.

2) Retrieve unread email for the user "Smith", from from our Exchange
Server, and insert it into a table.

3) Retrieve unread email for the user "Carrie", from from our Exchange
Server, and insert it into a table.

My initial thought is to create a DTS package to handle email
retrieval, and insert it into the table in the same step.

But I have no idea how to get started. How do I get DTS/SqlServer to
read mail for these three users?

`Le

You may be able to use this from Neil Pike ut I would probably be looking to do this a .Net or some such tool.

Q. How can I use SQL 7's HDQ facilities to get at NT domain or Exchange
information?
(v1.0 1999.04.22)

A. This information is supposed to get ADSI working from SQL Server, however
your mileage may vary (please provide any feedback on updating/correcting the
information here).


In order to get an ADSI linked server to SQL Server 7.0, you will need to
install the following pre-requisites. ADSI 2.0 doesn't work with MDAC 2.1
(installed w/ SQL Server 7.0)
For more information on the issues of ADSI 2.0 and MDAC 2.1, see:


http://support.microsoft.com/support.../q216/7/09.asp


You will have to install ADSI 2.5 on the SQL Server.


Requirements:
=============


1. You must install SQL Server 7.0. For more information about SQL Server,
visit http://www.microsoft.com/sql


2. You must install ADSI 2.5 Runtime (http://www.microsoft.com/adsi) -or-
(ftp://ftp.microsoft.com/bussys/sites...ixes/usa/sites...
/
sp2/x86/adsi/ads.exe) on the machine SQL Server is installed.


3. You must install Active Directory, (http://www.microsoft.com/windows).
You can also use


-or-


3. Exchange Server (http://www.microsoft.com/exchange)


Step by Step Instructions
======================
In to add a linked server, do the following:


1. Run the Query Analyzer


2. Logon the the SQL Server machine.


3. Execute the following line.


/************************************************** ***********/
sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject',
'adsdatasource'
go
/************************************************** ***********/


This tells SQL Server to associate word 'ADSI' with ADSI OLE DB provider -
'ADSDSOObject'


Following is an example scenario of common operations:
==============================*=================== =====


/************************************************** ***********/
SELECT * FROM OpenQuery(
ADSI,'<LDAP://DC=Microsoft,DC=*com>;(&(objectCategory=Person)*(o bjectClass=use
r));name, adspath;subtree')
/************************************************** ***********/


Note: you should change the DC=.., DC=.. accordingly. This query asks for
all users in the 'Microsoft.com' domain.


You may also use the ADSI SQL Dialect, for example:


/************************************************** ***********/
SELECT * FROM OpenQuery( ADSI, 'SELECT name, adsPath FROM
''LDAP://DC=Microsoft,DC=com'' WHERE objectCategory = ''Person'' AND
objectClass= ''user''')
/************************************************** ***********/


Creating, Executing a View
==========================


You may create a view for data obtained from Active Directory. Note that
only the view definition is stored in SQL Server, not the actual result
set. Hence, you may get a different result when you execute a view later.


To create a view, type and execute:


/************************************************** ***********/
CREATE VIEW viewADUsers AS
SELECT * FROM OpenQuery(
ADSI,'<LDAP://DC=Microsoft,DC=*com>;(&(objectCategory=Person)*(o bjectClass=use
r));name, adspath;subtree')
/************************************************** ***********/


To execute a view, type


/************************************************** ***********/
SELECT * from viewADUsers
/************************************************** ***********/


Heteregenous Join between SQL Server and Active Directory


Create a SQL table, a employee performance review table


/************************************************** ***********/
CREATE TABLE EMP_REVIEW
(
userName varChar(40),
reviewDate datetime,
rating decimal
)
/************************************************** ***********/


Insert few records


/************************************************** ***********/
INSERT EMP_REVIEW VALUES('Administrator', '2/15/1998', 4.5 )
INSERT EMP_REVIEW VALUES('Administrator', '7/15/1998', 4.0 )
/************************************************** ***********/


Note: You can insert other user names.


Now join the two


/************************************************** ***********/
SELECT ADsPath, userName, ReviewDate, Rating
FROM EMP_REVIEW, viewADUsers
WHERE userName = Name
/************************************************** ***********/


Now, you can even create another view for this join,


/************************************************** ***********/
CREATE VIEW reviewReport
SELECT ADsPath, userName, ReviewDate, Rating
FROM EMP_REVIEW, viewADUsers
WHERE userName = Name
Advanced Operations
/************************************************** ***********/


You may log-on as different user when connecting to the Active Directory.
To specify the alternate credential.


Example:


/************************************************** ***********/
sp_addlinkedsrvlogin ADSI, false, 'MICROSOFT\Administrator',
'CN=Administrator,CN=Users,DC=*Microsoft,DC=com', 'passwordHere'
/************************************************** ***********/


This line tells Distributed Query that if someone logs on in SQL Server as
'Microsoft\Administrator', the Distributed Query will pass the
'CN=Administrator,CN=Users, DC=Microsoft, DC=com' and 'passwordHere' to
ADSI as the credentials.


To stop connecting as an alternate credential, type:


/************************************************** ***********/
sp_droplinkedsrvlogin ADSI,'MICROSOFT\Administrator'
/************************************************** ***********/


The following links may also be helpful:
==============================*===========


http://msdn.microsoft.com/developer/.../Exchange/adsi...


http://msdn.microsoft.com/library/sd...9u.htm#_ds_for...
mmar_for_the_sql_queries


http://msdn.microsoft.com/library/sd...intro_0ckz.htm






On 17 Aug 2005 07:37:28 -0700, "Le" <TheLeGames (AT) gmail (DOT) com> wrote: