To do this, we need to configure a SQL Server to use Microsoft’s Mail API ( MAPI). It also serves as a good illustration of what’s possible using extended stored procedures.įor xp_sql2exchange to work, the SQL Server Service will need to act as a mail client to a Microsoft Exchange Server (if you will be using PSTs instead, you can follow the additional instructions mentioned later in this article for enabling PST access). On the other hand, the solution afforded by xp_sql2exchange requires no such specialized programming it uses ‘pure’ TRANSACT-SQL calls (based on a suite of routines developed in efficient compiled C++, which is what most of Microsoft’s programming tools and operating system components are also written with). But you will not be able to invoke ADO directly from stored procedures in SQL. But, unfortunately, at the time of this article’s publication, you cannot directly modify Exchange folders using this API.ĪDO is a database programming language API that can be used to alter Exchange Folders. There is a very useful API for retrieving Exchange information from SQL called " Exchange OLE DB" this API lets you query Folders as if they were SQL tables, using SELECT statements.
Without extensive knowledge of CDO or MAPI programming, a SQL developer can quickly and effectively add items to Public Folders in Exchange. The routines include xp_contact, xp_calendar, xp_task, and xp_journal for creating, as you might have suspected, Outlook Contacts, Appointments, Tasks, and Journal Entries, respectively.
Xp_sql2exchange is a collection of extended stored procedures that let you create and/or update Outlook Contacts, Appointments, Tasks, and Journal Entries in Exchange Public Folders (you can also create Exchange objects in the Folders of a standalone Outlook PST, but more on that later). To learn more about writing extended stored procedures, consult the Microsoft SQL Server Platform SDK, which is part of the Back Office SDK. For example, SQL Server has no intrinsic ability to send Email, but using a popular extended stored procedure called " xp_sendmail", one can send SMTP mail messages.
The purpose of extended stored procedures is to accommodate the addition of software capabilities that may not be a part of the standard collection of SQL function libraries. Presently, as of SQL Server 2000 Service Pack 3, extended stored procedures must be loaded in Microsoft SQL Server’s "master" database. By convention, extended stored procedure names begin with an " xp_…" prefix.
A free download is available for the extended stored procedures (enabled with basic functionality), along with sample scripts for implementing the table triggers and loading and unloading the extended stored procedures.Įxtended Stored Procedures: What are they?Īn extended stored procedure is a compiled program that can be invoked from SQL Server using the same calling conventions as a conventional SQL stored procedure. These procedures can be added to the trigger of a SQL database table, thus easily liberating your enterprise’s SQL data to Outlook if your organization is using Exchange Server updates made to a database table can be immediately displayed in an Exchange Public Folder. The following is an explanation of a suite of extended stored procedures you can use to create Outlook Contacts, Appointments, Tasks, and Journal Entries in Exchange Public Folders (or Outlook PSTs).
Have you ever wanted to publish your organization’s SQL Server data in Exchange so that everyone with Outlook could access it? But perhaps you didn’t have the time or interest to learn how to use Microsoft messaging APIs.