Imagine going through your inbox on Monday morning and reading a message from your SQL Server informing 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 and receive mail messages between Windows Messaging applications such as Microsoft Exchange, and Microsoft SQL Server 6.x.
With SQLMail, you can easily set up procedures within SQL Server to send plain-English alert messages to designated users. For example, you can have SQLMail forward nightly database maintenance logs to an administrator. Depending on your database application, you can define triggers that monitor various queries on sensitive data such as salary information. Also, users can send database queries 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 client and addresses the Exchange server. The result is an open, commodity-based approach to messaging that avoids using proprietary BackOffice application-specific components to share information. Let's examine what makes SQLMail tick and how to configure SQL Server for SQLMail integration with Windows Messaging.
Configuring SQLMail on the Server
SQLMail is a group of SQL Server extended stored procedures that let SQL Server interact with
external NT Server programs (in this case Exchange Server), an MS Mail postoffice, or an MS
Mail-compatible postoffice under NT, through sqlmap60.dll. The SQLMail procedures use NT's basic
MAPI mail functions, such as send mail, receive mail, and process mail. With SQLMail, you can
receive messages from the SQL Server for tasks such as alerts or triggers, or you can send SQL
queries for data retrieval through ordinary mail messages. SQLMail lets you set up a SQL Server as
an MS Mail-compatible client. This configuration lets the SQL Server send and receive mail with an
Exchange Server or MS Mail postoffice, both of which are MAPI compliant.
SQLMail functionality requires a pre-existing MS Mail-compatible postoffice, such as an Exchange Server mailbox, MS Mail postoffice, or an NT Mail postoffice. The configuration you choose determines how you must configure SQLMail connectivity. (For tips on how you can avoid problems when setting up SQLMail, see the sidebar, "Troubleshooting SQLMail Configuration.")
If you are using an existing Exchange Server, set up an Exchange user mailbox for the SQL Server to use as its message store. This account must match that of the MSSQLSERVER service in the NT Control Panel. Although you can use the default LocalSystem account to set up SQLMail with the SQL Server, I recommend that you use a valid domain account instead. This setup not only facilitates SQLMail functions but also is required if you use SQL database replication. Later, when you use advanced 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 SQL Server in the postoffice you want. In small environments, or those where an Exchange Server isn't available, you can set up an NT postoffice on the server. NT postoffices are a carryover from the Windows for Workgroups postoffice (WGPO). A WGPO is an MS mail-compatible postoffice that you can use for Windows Messaging within an NT domain. To set up an NT postoffice, select the icon for the MS Mail postoffice from the Control Panel. The first time you launch the icon, you'll get a prompt to 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 the postoffice. Once you've completed the required information, use Explorer to share the WGPO directory, 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 on
the NT server. If you're using Office 97, the Outlook client is also compatible. To create the
required Registry entries, you must configure the client on the NT server. First, configure a
Windows 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 SQL
Server. This profile must contain only information services for Microsoft Mail, Personal Address
Book, and Personal Folders.
Start the Windows Messaging Client (or Exchange if it's installed on the server), and log on to the 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 NT server 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 SQLMail icon. Select the Configure option, and enter the name of the messaging profile that you set up with the MS Mail service for the SQL Server. Click OK to complete the configuration, and right-click SQLMail again. This time, select Start, and the icon will change from red to green. SQLMail does not start any additional NT services, but the icon verifies that SQLMail has successfully connected to the MS Mail postoffice.
When configuring access to an Exchange Server, you set up the Exchange client profile in much the same way as Windows Messaging with MS Mail. The only exception, obviously, is that you configure the profile with the Exchange Server information service, rather than MS Mail. Click the Delivery tab 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 sure that Exchange is sending and receiving messages with the SQL Server account before you start the SQLMail icon.
Using Stored Procedures
Now that you've configured access to the mail client, you must understand how to use the various
SQLMail stored procedures listed in Table 1. To illustrate, I'll focus on some of the most common
SQLMail stored procedures. To have SQL Server send messages to Exchange, you have to use SQL
procedures or scripts to specify the appropriate parameters for the xp_sendmail command. You can use
Interactive SQL, with or without parameters, to enter procedures.
| Send mail between SQL Server and Windows Messaging applications such as Exchange |
The xp_sendmail command with parameters is most useful when you set it up in the SQL Executive as an alert task. To enable SQL Server to receive messages with embedded queries, use the same procedure with the @query parameter specified. With the sp_who command, shown in Screen 1, you can perform a simple query to show currently logged-on users.
The SQLMail stored procedures also let Exchange users send SQL database queries as standard mail messages. Users simply compose a standard mail message addressed to the Exchange account you've set up for the SQL Server. The message text must be a valid SQL query. Exchange won't provide plain-English translation. SQLMail then processes the query on the SQL Server with sp_processmail and returns the results as an attached file to the sender. You can find detailed information about the specific parameters required for xp_sendmail and the related SQLMail procedures in SQL Books Online.
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 only requires a message store for the SQL Server. The primary administrative issue to be aware of is that users must know that the SQL Server won't understand plain-English messages. Messages must contain valid SQL queries for the SQLMail components to process them. Additionally, you will want to limit which users are able to send mail to the SQL Server because an ad hoc query can bring SQL Server performance to a screeching halt.
Corresponding with SQL Server
Windows Messaging and SQL integration is a powerful capability that provides a foundation for
future mail-enabled enterprise applications. Combined with some well-written Exchange e-forms
or Outlook Office 97 forms, mail-enabled applications can provide robust and seamless interaction
within BackOffice applications. Those daily email conversations with your SQL Server may not be far
off.
