xp_sql2exchange

October 4, 2004

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 stored procedures.

Extended Stored Procedures: What are they?

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.

What is xp_sql2exchange?

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.

Installing xp_sql2exchange

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.

Creating a mail profile on the SQL Server

for the SQL Server Service Account

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.

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′,
                                @matchFileAs=′Jim Morrison′,
                                @FirstName=′Jim′, @LastName=′Morrison′,
                                @Phone=′(310)652-4202′,
                                @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 database tables.]

What if the Exchange Server is down?

Since 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?

You 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?

That 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 future article.

It 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…

Getting Help

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.

Standalone PSTs

You 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.

Learning more

Experience 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.