Generate Exchange Task from T-SQL

Our organization would like to add tasks to users' Exchange accounts from our SQL Server using a USP. Basically, we are looking for the same functionality as the xp_sendmail syntax provides, but instead of sending an email to a user, we would like to create a task in the user's Exchange Tasks folder based on the information passed from our database via the USP.

Here is an example:

A client must receive paperwork every 6 months based on a date stored in our SQL database.

2 weeks prior to the date the paperwork is due, a USP would detect that John Doe has upcoming paperwork needed.

Bob is John Doe's sales rep. The USP would create a new task in Bob's Exchange Tasks folder indicating that John Doe's paperwork is due on such-and-such a date, setting reminders, etc.

We are currently running SQL 2000 and Exchange 2003 in an Active Directory environment. Any help or pointers would be greatly appreciated!!

Thank you - Jeremy

Best is to use CDO (Colloborative Data Objects) or Outlook Object Data Model outside of the database. You could write code using sp_OA* SPs but it is not going to be a robust implementation. You can use a SQLAgent job with ActiveXScript task to do the task creation. See below links for more details on how to use CDO and Outlook object model.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/exchanchor/htms/msexchsvr_cdo_top.asp

http://msdn2.microsoft.com/en-us/library/ms268893.aspx

There are lots of KB articles that contains code for using CDO / Outlook Object Model to create messages, appointments, items, tasks etc.