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. 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). 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. 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
An 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. By convention, extended stored procedure names begin with an "xp_..." prefix. Presently, as of SQL Server 2000 Service Pack 3, extended stored procedures must be loaded in Microsoft SQL Server's "master" database.
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. 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. To learn more about writing extended stored procedures, consult the Microsoft SQL Server Platform SDK, which is part of the Back Office SDK.
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). 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. Without extensive knowledge of CDO or MAPI programming, a SQL developer can quickly and effectively add items to Public Folders in Exchange.
[The software discussed in this article is available for download at http://www.xpsql2exchange.com]
Why not just use Exchange OLE DB or ADO?
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. But, unfortunately, at the time of this article's publication, you cannot directly modify Exchange folders using this API.
ADO is a database programming language API that can be used to alter Exchange Folders. But you will not be able to invoke ADO directly from stored procedures in SQL. 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). It also serves as a good illustration of what's possible using extended stored procedures.
For 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). To do this, we need to configure a SQL Server to use Microsoft's Mail API (MAPI). If you are in a shop with more than one SQL Server, this package can be installed on just one of the SQL Servers, and that SQL Server can subsequently act as a "gateway" to Exchange.
To configure SQL Server as a mail client, the SQL Server Service (MSSQLSERVER) will need to be assigned to a Windows Account. If one does not exist already, create a Windows Account with Administrative privileges (or more restricted privileges should your security needs dictate and you know what will accommodate SQL Server's operation). In the examples here we reference a hypothetical account called "SQL" in a domain named "YOUR_DOMAIN". Change these values to whatever account you have decided to create for the SQL Server Service, and to whatever domain that your SQL Server will be a member of.
Configuring the SQL Server Service to run on a Windows Account
Go to Services in the Control Panel of the SQL Server upon which you want xp_sql2exchange installed (you can also configure Services remotely by using the Computer Management console in Window's Administrative Tools, then execute the menu command "Action, Connect to another computer...," and modify the Services there).
Double click on the MSSQLSERVER service, and go to the "Log On" tab (see Figure 1
Figure 1 : Configuring the SQL Server Service Account
If you are using the Local System account for the SQL Server Service, change this to the account that you have just created for SQL, and enter the password. To confirm that the SQL Server Service account has the proper credentials necessary to run the SQL Server Service, restart the SQL Server Service. If the service starts correctly, you might want to go to SQL Query Analyzer and double-check that some simple queries on the databases you use work correctly. Otherwise, make sure you are using the correct Windows Account and password, with all necessary administrative privileges that the Microsoft SQL Server Service might need.
Once you have configured the SQL Server Service successfully, you will need to set up a mail profile that will let your SQL Server communicate with Exchange. Log on to the SQL Server's console as the SQL Server Service Account (you might need to grant the "Log On Local" Right to the account to do this; it can be revoked once this installation is complete). Then go into Control Panel on the SQL Server and configure the SQL Server's Service account mail profile to use Exchange Server. If "Mail" does not appear in the Control Panel after you have logged onto the SQL Server console with the SQL Server Service Account, you will also have to install an Exchange Server Mail client on the SQL Server, such as Outlook.
As an added check, once the mail profile has been created on the SQL Server for the SQL Server Service Account, you can open the Exchange client (usually Outlook) while you are logged on as the SQL Server Service Account at the SQL Server console, to confirm that you can 'see' the Exchange Public Folders you want to modify, and perhaps even make a simple entry in the Public Folder to confirm that all Folder permissions are in order.
Note: Any Exchange Public Folders the
xp_sql2exchange suite of extended stored procedures will be modifying
will need to have Permissions set to allow the SQL Server Service Account to
alter the Folder, either implicitly through the use of "default"
or "anonymous" Public Folder Permissions, or explicitly through
the assignment of the SQL Server Service's Account to a Public Folder's
permissions. Just make sure that the SQL Server Service Account has
permission to update the Public Folder you are attempting to write to (right-click
on the Public Folder in Outlook, select Properties, and go to the Permissions
tab to set this up...).
Installing the Extended Stored Procedures in SQL Server
Included in the download you will find a SQL script called xp_add.sql, and a DLL called exchange.dll. Copy the DLL to your SQL Server's Binn\dll directory (for example, if you installed SQL Server on your "C:" drive, you will usually find this in "C:\Program Files\Microsoft SQL Server\MSSQL\Binn\dll"; do some file searching on your drives if it's not there.
SQL 2005 UPDATE : MAPI calls in stored procedures are not enabled by default in SQL Server 2005; after an installation of SQL 2005 with default options, a MAPI initialization error is raised when such calls are executed. You can use the new security tool, "SQL Server Surface Area Configuration", or the sp_configure stored procedure to enable or disable MAPI calls from stored procedures. To view this option using sp_configure, you have to first enable the "show advanced options" server configuration option.
Once the DLL is in that location, go into SQL Query Analyzer and run the xp_add.sql script furnished with this download (you can open it from the "File, Open" menu, then hit the green "Execute Query" right arrow on the toolbar). In the result window of Query Analyzer you should get back a message of "The command(s) completed successfully." If not, check to see that you copied the DLL to the correct location.
Figure 2 : Running xp_add.sql in the SQL Query Analyzer
Running xp_sql2exchange: a simple example
OK, all the hard work has now been done. To see the fruits of your efforts, open Outlook and the SQL Query Analyzer, and open the xp_contact.sql example included in the download. This is a very simple call that will create a Contact in an Exchange Public Folder:
Exec master.dbo.xp_contact @FolderPath='SQL_Contacts',
@Street='8901 West Sunset Boulevard',
@City='Los Angeles', @State='CA', @Zip='90069'
Modify the @FolderPath variable of the extended stored arguments ("SQL_Contacts") to whatever Public Folder Path with Contact Items that you would like to write to (or, make a Public Folder under "All Public Folders" in Outlook called "SQL_Contacts", made of Contact Items, and use the example script, as is; just don't forget to set the Folder's permissions to permit the SQL Server Service Account to access it...).
If everything went well, after you execute the query you should get back a message in the result window of Query Analyzer that states "The command(s) completed successfully", and a Contact should appear in the Public Folder of Outlook. Otherwise, an informative error message is usually reported (when any of the xp_sql2exchange extended stored procedures fail, as functions they return error values, and the error values can be evaluated in your SQL scripts; you can then program for error contingencies accordingly).
Figure 3 : Running xp_contact (Outlook and SQL Query Analyzer)
Generally, procedures in xp_sql2exchange use one or more "match" arguments to find a matching Exchange object (be it a Contact, Appointment, Task, or Journal Entry) by using a prefix of "match..."; as in "@matchFileAs", used by the xp_contact extended stored procedure to indicate all Contacts whose FileAs matches that extended stored procedure's argument value. [The "match..." arguments supported differ from command to command; consult each command's Usage (see below) to find out more.] If the procedure finds an object that matches on that field or those fields (be it one or more "match" arguments), it modifies it according to the rest of the arguments furnished to the procedure; otherwise, it creates just one Exchange item with its fields set to whatever arguments were furnished to this extended stored procedure. By using this routine exclusively to update Public Folders in Exchange, it can insure one version of an object.
Included in the free software download are two sets of example scripts that will manage Contact updates based on either a unique Id or Contact's Full Name in SQL. One set will do a simple bulk export of a database table with Contact information to an Exchange Public Folder (bulk_id.sql or bulk_name.sql);
these scripts could be scheduled to run on a periodic basis. Another set of
examples use SQL trigger script code (trigger_id.sql or trigger_name.sql)
that can be used to instantly and automatically update an Exchange Public
Folder whenever the records in a SQL database table of Contact information are
updated. Keep in mind that the principles used in these scripts for updating
Contacts can also be extrapolated to updating Appointments, Tasks, and Journal
Entries. [An elementary knowledge of SQL trigger scripts is recommended if
you are going to use this sample code; see the articles mentioned at the end of
this article for pointers on how to manage and program triggers on SQL Server
What if the Exchange Server is down?
the xp_sql2exchange extended stored procedures return success codes, you
can do clever things using replication scripts to insure that not an update is
lost should the network connection between your SQL Server and Exchange Server
go out (or in the unfortunate scenario of a complete Exchange Server outage).
Using one table as a queue for holding all updates to be applied to an Exchange
Public Folder, the records will not be replicated to another table indicating
that the update was applied to the Exchange Server until the xp_sql2exchange
stored procedures (hooked into a replication trigger script of the "update
queue" table) signal success.
What about updates caused by Exchange Clients?
can make a simple stored procedure that invokes this extended stored procedure
on a scheduled basis, to update Public Folders in Exchange. You can also invoke
these procedures from triggers on SQL tables to have database modifications automatically
reflected in Exchange Public Folders. Great: that gets information from SQL to
Exchange. A DBA with just a basic understanding of writing SQL Server stored
procedures can now make an Enterprise's data visible to all of its Outlook
clients. But what if somebody in Outlook modifies an item in an Exchange Public
Folder from his or her Outlook? How does SQL Server get that information stored
back into its database tables?
case is not handled by this package. To cover this contingency, you also need
what is called an Exchange Event Folder Sink to trap updates from
an Exchange Public Folder and post them back to SQL database tables. The author
has done it, but it is beyond the scope of this project, and requires
configuration on Exchange, which implies a familiarity with Exchange
administration. The intended audience for the xp_sql2exchange package is
SQL DBAs (take note that all requisite configuration for this package was done
from just the Outlook client). This capability may be discussed in another
is recommended that the Folders populated with these extended stored procedures
be set to read-only for the people intending to reference this information,
unless you are going to work out the details of interleaving the Outlook client
updates that your users might post to Public Folders...
So what are all the arguments to these extended stored procedures? How do you know which to use??
By simply running any of the commands of the xp_sql2exchange package in SQL Query Analyzer without arguments, you get a "Usage" that documents what all the arguments are. Below is a screen shot for xp_contact; you can also run xp_calendar, xp_task, and xp_journal without arguments to get the online documentation for these commands. The Usage Help is best viewed using a fixed size font in the SQL Query Analyzer, such as Lucida Console (this can be set from the menu commands (Tools, Options, Fonts...; note that SQL Server usually defaults on install to using a fixed size font in SQL Query Analyzer).
Figure 4 : Usage for xp_contact (SQL Query Analyzer)
Note:The version of xp_sql2exchange
available for free download supports the alteration of a Contact's Name,
Address, Phone Number, and Email Fields. Also, Appointments, Tasks, and Journal
Entries can be created but not modified or deleted. A "full" version is
available that allows you to have access to all Contact fields and the
additional capabilities of modification and deletion for Appointments, Tasks,
and Journal Entries. The author feels that the functionality delivered in the
free download is of enough value to be of help to many SQL DBAs seeking to
liberate data from an enterprise's SQL Server(s) to its Exchange and Outlook clients.
The package also serves as a good example of what is possible using extended
stored procedures, which you or your organization can develop.
can have xp_sql2exchange update a standalone Outlook PST, as
opposed to an Exchange Server Public Folder. This can be helpful if you are
developing on a workstation using, say, MSDE (SQL Server Desktop
Engine) and do not have ready access to an Exchange Server. You choose PST
Folders instead of an Exchange Server for the Mail Profile in Control Panel
(see the install section of this article above), and then reference the PST
using an Outlook client. The @FolderPath variables will then refer to
Folders in your standalone Outlook mail profile (such as "Contacts",
"Calendar", etc., or any other Folders you might create in the "Personal
Folders" of the Outlook profile of the SQL Server's Service Account). You
could also possibly use this as a way of representing SQL Server data in
meaningful ways to individual users with a minimum of development effort.
is the best teacher, so try some examples interactively in SQL Query Analyzer
with Outlook open and see how this suite of procedures works. And maybe you
will start to think about some other extended stored procedures you would like
to see and might develop, or have others in your organization develop.
Triggers in SQL
Server 7.0 and 2000 - The Common Ground
Quickly Enabling and Disabling Constraints and Triggers
Simplifying "Instead Of" Triggers
Exploring SQL Server Triggers
Using Triggers In MS SQL Server
An Introduction to Triggers
SQL Server 2000 Trigger Enhancements
OLE DB Provider for Exchange
About the Publisher
xp_sql2exchange was developed by Logicom (New York)
Logicom specializes in delivering software tools and
applications that leverage the productivity of software development processes
and projects. Logicom is a multi-disciplinary developer, embracing both Open
Source and proprietary software development technologies, always taking into consideration
the price-performance value of the functionality delivered by each and every