Send SQL Server Data to Exchange

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.

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',
    @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.

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.

About the Publisher

xp_sql2exchange was developed by Logicom (New York)

[website http://www.logicom-inc.com]

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

software environment.

Send SQL Server Data to Exchange

Extended Stored Procedures: What are
they?