Integrating Windows Messaging with SQL Server

Imagine going through your inbox on Monday morning and reading a message from your SQL Serverinforming you that someone has accessed confidential salary information from the corporate database.This messaging scenario is possible with SQLMail, which comes with SQL Server. You can send andreceive mail messages between Windows Messaging applications such as Microsoft Exchange, andMicrosoft SQL Server 6.x.

With SQLMail, you can easily set up procedures within SQL Server to send plain-English alertmessages to designated users. For example, you can have SQLMail forward nightly database maintenancelogs to an administrator. Depending on your database application, you can define triggers thatmonitor various queries on sensitive data such as salary information. Also, users can send databasequeries to SQL Server through standard mail messages.

Figure 1 shows Microsoft's building-block approach to SQL and Exchange integration:SQL builds on Messaging API (MAPI), which is part of Windows NT. NT acts as a standard mail clientand addresses the Exchange server. The result is an open, commodity-based approach to messaging thatavoids using proprietary BackOffice application-specific components to share information. Let'sexamine what makes SQLMail tick and how to configure SQL Server for SQLMail integration with WindowsMessaging.

Configuring SQLMail on the Server
SQLMail is a group of SQL Server extended stored procedures that let SQL Server interact withexternal NT Server programs (in this case Exchange Server), an MS Mail postoffice, or an MSMail-compatible postoffice under NT, through sqlmap60.dll. The SQLMail procedures use NT's basicMAPI mail functions, such as send mail, receive mail, and process mail. With SQLMail, you canreceive messages from the SQL Server for tasks such as alerts or triggers, or you can send SQLqueries for data retrieval through ordinary mail messages. SQLMail lets you set up a SQL Server asan MS Mail-compatible client. This configuration lets the SQL Server send and receive mail with anExchange Server or MS Mail postoffice, both of which are MAPI compliant.

SQLMail functionality requires a pre-existing MS Mail-compatible postoffice, such as anExchange Server mailbox, MS Mail postoffice, or an NT Mail postoffice. The configuration you choosedetermines how you must configure SQLMail connectivity. (For tips on how you can avoid problems whensetting up SQLMail, see the sidebar, "Troubleshooting SQLMail Configuration.")

If you are using an existing Exchange Server, set up an Exchange user mailbox for the SQLServer to use as its message store. This account must match that of the MSSQLSERVER service in theNT Control Panel. Although you can use the default LocalSystem account to set up SQLMail with theSQL Server, I recommend that you use a valid domain account instead. This setup not only facilitatesSQLMail functions but also is required if you use SQL database replication. Later, when you useadvanced SQL functionality, this setup will reduce headaches.

If you're still using MS Mail, you must use the ADMIN program to set up a mail account for SQLServer in the postoffice you want. In small environments, or those where an Exchange Server isn'tavailable, you can set up an NT postoffice on the server. NT postoffices are a carryover from theWindows for Workgroups postoffice (WGPO). A WGPO is an MS mail-compatible postoffice that you canuse for Windows Messaging within an NT domain. To set up an NT postoffice, select the icon for theMS Mail postoffice from the Control Panel. The first time you launch the icon, you'll get a promptto connect to an existing postoffice or create a new one. Select the option to create a postoffice,and you'll see a prompt for the administrator account details that you'll use to administer thepostoffice. Once you've completed the required information, use Explorer to share the WGPOdirectory, and make sure that the MSSQLSERVER account has sufficient privileges to access the share.

Configuring the SQLMail Client
The next step in setting up SQLMail is configuring the Windows Messaging or Exchange client onthe NT server. If you're using Office 97, the Outlook client is also compatible. To create therequired Registry entries, you must configure the client on the NT server. First, configure aWindows Messaging profile for testing basic mail send and receive functionality outside SQL Server.From Control Panel, open the Mail and Fax icon. Click Show Profiles, and add a profile for the SQLServer. This profile must contain only information services for Microsoft Mail, Personal AddressBook, and Personal Folders.

Start the Windows Messaging Client (or Exchange if it's installed on the server), and log on tothe postoffice with the account created for the SQL Server. Compose a simple message, and send it.Make sure that the recipient gets the message. You must perform this step while logged on to the NTserver with the same user account that the MSSQLServer service is started with.

Next, start the SQL Enterprise Manager, connect to the SQL Server, and right-click the SQLMailicon. Select the Configure option, and enter the name of the messaging profile that you set up withthe MS Mail service for the SQL Server. Click OK to complete the configuration, and right-clickSQLMail again. This time, select Start, and the icon will change from red to green. SQLMail does notstart any additional NT services, but the icon verifies that SQLMail has successfully connected tothe MS Mail postoffice.

When configuring access to an Exchange Server, you set up the Exchange client profile in muchthe same way as Windows Messaging with MS Mail. The only exception, obviously, is that you configurethe profile with the Exchange Server information service, rather than MS Mail. Click the Deliverytab and make sure that the mail delivery location is the online Exchange mailbox for the SQL Server;make sure that this profile does not contain personal folders. As with the MS Mail setup, make surethat Exchange is sending and receiving messages with the SQL Server account before you start theSQLMail icon.

Using Stored Procedures
Now that you've configured access to the mail client, you must understand how to use the variousSQLMail stored procedures listed in Table 1. To illustrate, I'll focus on some of the most commonSQLMail stored procedures. To have SQL Server send messages to Exchange, you have to use SQLprocedures or scripts to specify the appropriate parameters for the xp_sendmail command. You can useInteractive SQL, with or without parameters, to enter procedures.

The xp_sendmail command with parameters is most useful when you set it up in the SQL Executiveas an alert task. To enable SQL Server to receive messages with embedded queries, use the sameprocedure with the @query parameter specified. With the sp_who command, shown in Screen 1, you canperform a simple query to show currently logged-on users.

The SQLMail stored procedures also let Exchange users send SQL database queries as standardmail messages. Users simply compose a standard mail message addressed to the Exchange account you'veset up for the SQL Server. The message text must be a valid SQL query. Exchange won't provideplain-English translation. SQLMail then processes the query on the SQL Server with sp_processmailand returns the results as an attached file to the sender. You can find detailed information aboutthe specific parameters required for xp_sendmail and the related SQLMail procedures in SQL BooksOnline.

A look at the Exchange Server side to this equation doesn't reveal any huge mysteries.Remember, most of the functionality comes with SQL Server and NT. The Exchange postoffice onlyrequires a message store for the SQL Server. The primary administrative issue to be aware of is thatusers must know that the SQL Server won't understand plain-English messages. Messages must containvalid SQL queries for the SQLMail components to process them. Additionally, you will want to limitwhich users are able to send mail to the SQL Server because an ad hoc query can bring SQL Serverperformance to a screeching halt.

Corresponding with SQL Server
Windows Messaging and SQL integration is a powerful capability that provides a foundation forfuture mail-enabled enterprise applications. Combined with some well-written Exchange e-formsor Outlook Office 97 forms, mail-enabled applications can provide robust and seamless interactionwithin BackOffice applications. Those daily email conversations with your SQL Server may not be faroff.