SQL Server E-mail

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Columns & Rows

An Introduction to SQL Mail and SQLAgentMail

By Mike Gunderloy

Making connections between software applications is in style. XML and .NET are just the most visible components of a drive to make everything talk to everything else. We're all in for a lot of learning over the next few years while we figure out how to make our applications work together as Web services. Sometimes, though, it's worth remembering that older technology can still work very well. One such tried and true technology is the connection between Microsoft SQL Server and e-mail.

SQL Server 2000 and SQL Server 7.0 are fully integrated with e-mail. You can use SQL Server's e-mail integration to perform a variety of tasks:

• Accept queries via e-mail
• Send results back via e-mail
• Notify operators of job success or failure via e-mail or pager

In this article, you will learn how to set up and use the e-mail services in Microsoft SQL Server 2000. Most of what you'll learn applies equally well to Microsoft SQL Server 7.0. You'll discover that it's actually rather simple to integrate your back-end database with your e-mail system.

Concepts and Architecture

SQL Server 2000 divides its e-mail connectivity into two distinct services, SQL Mail and SQLAgentMail. Both establish separate MAPI connections with a mail host, even if they both use the same mail account on the same host.

SQL Mail provides a Transact-SQL (T-SQL) interface to e-mail, both incoming and outgoing. This is done through a set of stored procedures:

• xp_startmail
• xp_sendmail
• xp_findnextmsg
• xp_readmail
• xp_deletemail
• sp_processmail
• xp_stopmail

SQL Mail won't do anything by itself. You have to explicitly invoke the proper stored procedures through your own T-SQL code. You might do this from a stored procedure or a trigger, or as part of a scheduled SQL Server job.

SQLAgentMail, in contrast, provides basic mail services for the SQL Server Agent service. SQLAgentMail is used to send SQL Server alerts to e-mail (and pager) addresses, and to send notifications of job success or failure as well. Any message sent to a SQL Server operator by e-mail or pager uses SQLAgentMail.

As far as SQL Server is concerned, pager addresses are just alternative e-mail addresses. For SQL Server to send messages to operator pagers, those pagers must be addressable through an e-mail gateway (almost any pager you can sign up for these days has an e-mail gateway, so that shouldn't be a problem). You can also use the pager address of an operator to hold a second e-mail address for notification that doesn't go to a pager; SQL Server has no way of knowing what's on the other end of an e-mail message.

Setting up SQL Server E-mail

To get started with SQL Server e-mail, you need to set up a mail profile that associates an e-mail account with the account that you use to run SQL Server. Log on to the computer where SQL Server is running. Enter the account name and password used by the MSSQLSERVER service. If you're unsure which account this is, you can check it with the Services plug-in for Microsoft Management Console, which you'll find on the Administrative Tools menu in Windows 2000. Then open Control Panel, double-click the Mail icon, and create a mail profile. (If you've installed Microsoft Outlook on the SQL Server computer, you may not find a Mail icon in Control Panel. In that case, right-click on the Outlook icon on the desktop and choose Properties to configure an e-mail profile). SQL Server uses extended MAPI to communicate with the mail server. In practice, this means that a Microsoft Exchange server or a POP3 server should work just fine for your SQL e-mail needs.

If you want to use a separate e-mail account for SQLAgentMail, log on to Windows with the account that the SQLSERVERAGENT service uses, and create a mail profile for that account to use as well.

The next step is to associate the mail profiles you've just created with the SQL Server services. To do this, launch SQL Server Enterprise Manager (at this point, you can be logged on as any user who has administrative control over the SQL Server). Expand the Support Services folder for your server. Right-click on the SQL Mail service and choose Properties. This will open the SQL Mail Configuration dialog box shown in FIGURE 1. Select the profile that you created for the service, click on Test to make sure that SQL Server can communicate with the server, and then click OK.

FIGURE 1: Configuring SQL Mail.

You'll also need to set the mail profile for the SQL Server Agent service. To do this, expand the Management folder of your server with SQL Server Enterprise Manager, right-click on the SQL Server Agent node, and select Properties. FIGURE 2 shows where you can select the mail profile within the resulting dialog box. You can use the same profile you used for SQL Mail, or a different one.

FIGURE 2: Configuring mail for SQL Server Agent.

Using SQL Server E-mail from T-SQL

To interact with SQL Server e-mail, you can invoke a set of stored procedures. Most of these are extended stored procedures contained within sqlmap.dll. The exception is sp_processmail, which is a stored procedure in the master database that you can modify if you'd like to customize the way that your SQL Server handles incoming mail.

To start a SQL Mail session, call the xp_startmail extended stored procedure. Here's its syntax:

xp_startmail [[@user =] 'mapi_profile_name'] 
   [,[@password =] 'mapi_profile_password'] 

As you can probably guess, xp_startmail starts a new MAPI session that you can use from your T-SQL code. If you don't supply the **@user** and **@password** arguments, SQL Mail will use the profile that you specified as part of SQL Mail properties. You can override this by specifying another profile name; depending on your mail server, you may also have to specify the password that goes with the profile.

The xp_startmail extended stored procedure will return the message, SQL Mail session started, when it is executed successfully. Once you've started a SQL Mail session, you can use the xp_sendmail stored procedure (see FIGURE 3) to send a message.

xp_sendmail {[@recipients =] 'recipients [;...n]'}
   [,[@message =] 'message']
   [,[@query =] 'query']
   [,[@attachments =] 'attachments [;...n]'] 
   [,[@copy_recipients =] 'copy_recipients [;...n]'
   [,[@blind_copy_recipients =]
    'blind_copy_recipients [;...n]'
   [,[@subject =] 'subject']
   [,[@type =] 'type']
   [,[@attach_results =] 'attach_value'] 
   [,[@no_output =] 'output_value'] 
   [,[@no_header =] 'header_value'] 
   [,[@width =] width]
   [,[@separator =] 'separator'] 
   [,[@echo_error =] 'echo_value'] 
   [,[@set_user =] 'user']
   [,[@dbuse =] 'database']

FIGURE 3: Syntax for the xp_sendmail stored procedure.

Although xp_sendmail has many parameters, they fit naturally into two groups. First, there are the parameters listed in the table in FIGURE 4. These parameters are concerned with the e-mail nature of the stored procedure. Second, there are the parameters listed in the table in FIGURE 5. These are the parameters that are concerned with the database nature of the stored procedure.

FIGURE 4: E-mail parameters to xp_sendmail**.**

FIGURE 5: Database parameters to xp_sendmail**.**

A typical use of xp_sendmail might look like this:

xp_sendmail 
  @recipients = 'MikeG1@larkfarm.com', 
  @subject = 'Query results', 
  @query = 'SELECT CustomerID, CompanyName FROM Customers', 
  @message = 'Here are your query results', 
  @attach_results = FALSE, 
  @dbuse = 'Northwind'

FIGURE 6 shows the resulting mail message as opened by Microsoft Outlook. When xp_sendmail succeeds, it returns the message, Mail sent, to the calling session.

FIGURE 6: Receiving an e-mail message with query results from SQL Server.

As you might expect, SQL Mail can read messages as well as send them. There are two extended stored procedures involved in the reading process. The first is xp_findnextmsg, which is used to discover message IDs of messages waiting in the SQL Server mail account inbox:

xp_findnextmsg [[@type =] type]
   [,[@unread_only =] 'unread_value'] 
   [,[@msg_id =] 'message_number' [OUTPUT]] 

The use of the xp_findnextmsg extended stored procedure is fairly simple. The three parameters are the MAPI type (which you will almost certainly leave empty), whether to consider only unread messages, and the MAPI message ID with which to start reading. If it finds a message with a message ID higher than the one you specified, xp_findnextmsg will return that message ID. Why do you want this? Because you'll need it for the more useful stored procedure xp_readmail, as shown in FIGURE 7.

xp_readmail [[@msg_id =] 'message_number'] 
   [,[@type =] 'type' [OUTPUT]] 
   [,[@peek =] 'peek'] 
   [,[@suppress_attach =] 'suppress_attach'] 
   [,[@originator =] 'sender' OUTPUT] 
   [,[@subject =] 'subject' OUTPUT] 
   [,[@message =] 'message' OUTPUT] 
   [,[@recipients =] 'recipients [;...n]' OUTPUT] 
   [,[@cc_list =] 'copy_recipients [;...n]' OUTPUT] 
   [,[@bcc_list =] 'blind_copy_recipients [;...n]' OUTPUT] 
   [,[@date_received =] 'date' OUTPUT] 
   [,[@unread =] 'unread_value' OUTPUT] 
   [,[@attachments =] 'attachments [;...n]' OUTPUT]) 
   [,[@skip_bytes =] bytes_to_skip OUTPUT] 
   [,[@msg_length =] length_in_bytes OUTPUT] 
   [,[@originator_address =] 'sender_address' OUTPUT]] 

FIGURE 7: Syntax for the xp_readmail stored procedure.

The xp_readmail extended stored procedure is in many respects the inverse of xp_sendmail: It accepts OUTPUT parameters that can be used to hold all of the important bits and pieces of an incoming e-mail message. The table in FIGURE 8 shows the parameters for xp_readmail.

FIGURE 8: Parameters for xp_readmail**.**

In addition to populating the supplied parameters, xp_readmail also populates a result set with the messages read. If you call xp_readmail without an **@msg\_id** parameter, you'll get a result set containing all of the messages waiting for the server mail account.

The **@message** parameter of xp_readmail is limited to 255 bytes. To read a message longer than this, you'll need to use the **@skip\_bytes** parameter and read the message by sections in a loop. Microsoft provides an example of this technique, as shown in FIGURE 9.

USE master WHILE (1 = 1) BEGIN
  EXEC @status = xp_readmail
     @msg_id = @msg_id, 
    @message = @message OUTPUT, 
    @skip_bytes = @skip_bytes OUTPUT, 
    @msg_length = @msg_length OUTPUT
  
  IF @status <> 0 BREAK
  
  SELECT 'msg_id' = @msg_id, 'msg_part' = @message
  
  IF @skip_bytes = @msg_length BREAK
END

FIGURE 9: Using the @skip\_bytes parameter to read message sections in a loop.

When you've read a message and are ready to dispose of it, you can call the xp_deletemail extended stored procedure:

xp_deletemail {'message_number'}

As you can see, xp_deletemail is simple. It just takes the MAPI message ID from xp_findnextmsg or xp_readmail and deletes the corresponding message.

Finally, when you're all done with the mail session, you can call the xp_stopmail extended stored procedure to release the session:

xp_stopmail

Putting the Pieces Together

Now you've seen all the pieces that you can use to both receive and send messages through e-mail from SQL Server, and perhaps you've realized that the extended stored procedures are powerful enough to close the loop. That is, you can have users send queries by e-mail and return the corresponding result sets by using xp_readmail to parse the incoming requests and xp_sendmail to send back the results. You don't need to set this all up yourself. SQL Server provides a built-in stored procedure to do just that:

sp_processmail [[@subject = ] 'subject']
   [,[@filetype = ] 'filetype']
   [,[@separator = ] 'separator']
   [,[@set_user = ] 'user']
   [,[@dbuse = ] 'dbname']

The sp_processmail stored procedure goes through all of the mail waiting in the SQL Server's inbox. If the **@subject** parameter is not null, it only processes messages with the specified subject; otherwise, it processes all messages in the inbox. SQL Server opens each message, interprets the text as a SQL Server query, runs the query and returns the results to the sender. If **@filetype** is supplied, it's used as the extension of the attached file containing results; it defaults to ".txt". The **@separator** parameter is passed to xp_sendmail. The **@set\_user** and **@dbuse** parameters govern the user and database context for running the queries. They default to the user who invokes sp_processmail and the master database, respectively.

By using SQL Server Agent to schedule a job that invokes sp_processmail periodically, you can turn your SQL Server into an automated e-mail query processing system.

Security

Of course, before you set up sp_processmail to run on a regular basis, you should spend a few minutes thinking about the security implications of this move. Suppose some Evil User from outside your company were to discover that you were running SQL Server Mail and sent this query to be processed:

DELETE * FROM ImportantTable

SQL Server would happily delete the data, and your database might be in serious trouble. Similar considerations apply to INSERT and UPDATE queries that can modify data. And, of course, you can also execute stored procedures this way.

I can suggest two ways to limit the possible damage that can be done by sp_processmail. The first is to be very careful about the context where this procedure runs. Set up a user account that has only SELECT permissions, and only on the tables that you wish to make available by e-mail. Then use the **@set\_user** parameter of sp_processmail to make sure that this user is the one who actually runs queries that are mailed in.

Sometimes this straightforward approach to security won't work. For example, one of my clients wanted to insert rows into a table by e-mailing the data from a Web server. In this case, we set up stored procedures to do the insert, and modified the way that sp_processmail itself works. You can use sp_helptext in the master database to view the actual code for sp_processmail, as shown in Listing One.

You can modify sp_processmail by adding additional checks or removing the call to xp_sendmail entirely. For example, you could limit sp_processmail to stored procedures using a particular naming convention with this code:

if left(@query,2) = 'sp'
  exec(@dbuse + '..' + @query) 

You can also check for a particular message sender or subject before processing a query. Of course, none of this is foolproof as e-mail messages can be forged, but you can certainly make it more difficult for someone to take advantage of query by e-mail to do bad things.

Sending Mail from Triggers and Stored Procedures

Of course, there's no reason why your server has to wait for incoming queries before sending out e-mail. There are many situations where it's useful to have SQL Server send mail notifying people of important events, or making periodic reports. You can use the SQL Server Mail stored procedures from triggers and stored procedures in these situations.

For example, suppose your accounting department needs to be notified whenever there's a new customer added to your database. You could accomplish this by writing an INSERT trigger on the Customers table, as shown in FIGURE 10.

TRIGGER NewCustomer ON Customers
FOR INSERT
AS
  DECLARE @custid varchar(5) 
  
  SELECT @custID = CustomerID FROM inserted
  
  EXEC master..xp_startmail
  EXEC master..xp_sendmail
    @recipients =
      'Accounting@mycorp.com', @subject = @custid, 
    @message = 'A new customer has been added.'
  EXEC master..xp_stopmail

FIGURE 10: INSERT trigger for the Customers table.

Unfortunately, in this situation xp_sendmail doesn't have direct access to the special inserted temporary table (because it's running in its own process), so you have to resort to the workaround shown to actually include data from the new record.

You can also use SQL Server Mail from a stored procedure. This provides an easy way to put daily reports in someone's inbox. For example, you might run a stored procedure like the one shown in FIGURE 11.

CREATE PROC spMailReport
AS
  EXEC master.dbo.xp_startmail
  EXEC master.dbo.xp_sendmail 
    @recipients = 'boss@mycorp.com', 
    @subject = 'Daily report', 
    @message = 'New Customers through midnight yesterday', 
    @query = 'EXEC spDailyReport, 
    @attach_results = 'FALSE'
  EXEC master.dbo.xp_stopmail

FIGURE 11: Stored procedure sends daily reports.

SQLAgentMail

To round out SQL Server's mail abilities, there's SQLAgentMail, which handles e-mail from jobs and alerts. You've already seen how to set up SQLAgentMail with a mail profile. The next step is to create a SQL Server operator for each e-mail address that you want to be able to notify. To do this, expand the Management folder within SQL Server Enterprise Manager, and select Operators under SQL Server Agent. Use the New Object button on the toolbar to create a new operator. FIGURE 12 shows the New Operator Properties dialog box. Note that you can supply both a regular and a pager e-mail address, and test them both.

FIGURE 12: Creating a new SQL Server operator.

Once you've created an operator, you can associate that operator with SQL Server jobs or alerts. To notify an operator when a job is finished, open the property sheet for the job, select the Notifications tab, check the E-Mail Operator box, and choose the appropriate operator. You can choose to send e-mail notifications when a job succeeds, when it fails, or whenever it is completed.

To notify an operator when an alert is triggered, open the alert's property sheet, select the Response tab, and check the E-mail box for the appropriate operator. As shown in FIGURE 13, you can choose to include additional text in any e-mail that is sent from an alert. Note also the Delay between responses controls at the bottom of this property sheet. It's possible for some alerts to be triggered many times in quick succession. For example, if a busy server runs out of disk space, every operation can trigger an alert. The delay between responses limits the number of e-mail messages that are sent in such a situation to avoid flooding the operator's inbox.

FIGURE 13: Configuring an alert to send e-mail.

Both jobs and alerts let you specify one operator for e-mail and another for paging. If you need to notify more than two people of a job completion or an alert, create an operator whose e-mail address is a distribution list on your mail server.

Version Differences

For the most part, SQL Mail support is identical in SQL Server 7.0 and SQL Server 2000. Here are the few differences:

• The **@query** and **@message** parameters of xp_readmail and xp_sendmail have been expanded from 255 bytes in SQL Server 7.0 to 8,000 bytes in SQL Server 2000.
• SQL Server 7.0 performs all MAPI connections with a single profile. SQL Server 2000 allows using separate profiles for SQL Mail and SQLServerAgentMail.
• SQL Server 7.0 allows starting a SQLMail session whenever the SQL Server service was started. SQL Server 2000 requires you to call xp_startmail to start a session.

How SQL Server E-mail Fits in with Office

So, why would you, an Office developer, want to use e-mail from SQL Server? After all, Office itself has programmable e-mail in the form of Outlook, and good e-mail integration from the other component programs.

If you're using SQL Server as your data storage back end, SQL Mail should be your first choice any time you need integrated e-mail functionality. By putting the e-mail duties on the back end instead of in the client, you gain several things. First, you can ensure that the e-mail process is consistent, no matter how the data is accessed. Second, you can ensure that the processing load for the e-mail is concentrated on the server instead of on the desktop.

For example, suppose you're using an Access ADP file to work with SQL Server data through a user-friendly front end. You might want to send notification to the accounting department when a new customer is added. In this case, your choice is to write Access VBA code that calls the SendMail method, or to write a trigger as demonstrated earlier. In most circumstances, you should choose the trigger. That gives you better control of the source of the mail, removes any uncertainties from the process (is the user logged in to their e-mail account, for example), and protects the process from future changes. If you choose later on to migrate the customer addition form to a Web page, the server side e-mail will still work fine without any further programming.

So, next time you need e-mail as part of your database processing, consider SQL Server e-mail. You'll find it to be simple, reliable, and robust.

Mike Gunderloy (MikeG1@mcwtech.com) is a Senior Consultant with MCW Technologies, a Microsoft Solution Provider. He's also the author of Visual Basic Developer's Guide to ADO (SYBEX, 1999) and Visual Basic and VBA Developer's Guide to the Windows Installer (SYBEX, 2000).

Begin Listing One - Default code for sp_processmail

create procedure sp_processmail
  @subject varchar(255) = NULL, 
  @filetype varchar(3) = 'txt', 
  @separator varchar(3) = 'tab', 
  @set_user varchar(132) = 'guest', 
  @dbuse varchar(132) = 'master'
as
  declare @status int
  declare @msg_id varchar(64) 
  declare @originator varchar(255) 
  declare @cc_list varchar(255) 
  declare @msgsubject varchar(255) 
  declare @query varchar(8000) 
  declare @messages int
  declare @mapifailure int
  declare @resultmsg varchar(80) 
  declare @filename varchar(12) 
  declare @current_msg varchar(64) 
  
  select @messages=0
  select @mapifailure=0
  
  if @separator='tab' select @separator=CHAR(9) 
  
  /* Get first message id. */
  exec @status = master.dbo.xp_findnextmsg
    @msg_id=@msg_id output, 
    @unread_only='true'
  
  if @status <> 0
    select @mapifailure=1
  
  while (@mapifailure=0) begin
    if @msg_id is null break
    if @msg_id = '' break
    exec @status = master.dbo.xp_readmail
      @msg_id=@msg_id, 
      @originator=@originator output, 
      @cc_list=@cc_list output, 
      @subject=@msgsubject output, 
      @message=@query output, 
      @peek='true', 
      @suppress_attach='true'
    if @status <> 0 begin
      select @mapifailure=1
      break
    end
    /* Get new message id before processing and   
       deleting current message. */
    select @current_msg=@msg_id
    exec @status = master.dbo.xp_findnextmsg
      @msg_id=@msg_id output, 
      @unread_only='true'
  
    if @status <> 0 begin
      select @mapifailure=1
    end
  
    if ((@subject IS NULL) OR
         (@subject=@msgsubject)) begin
      /* Generate random filename. */
      select @filename = 'SQL' +
         convert(varchar,ROUND(RAND()*100000,0)) +
        '.' + @filetype
      exec @status = master.dbo.xp_sendmail
        @recipients=@originator, 
        @copy_recipients=@cc_list, 
        @message=@query, 
        @query=@query, 
        @subject='Query Results', 
        @separator=@separator, 
        @width=256, 
        @attachments=@filename, 
        @attach_results='true', 
        @no_output='false', 
        @echo_error='true', 
        @set_user=@set_user, 
        @dbuse=@dbuse
  
      if @status <> 0 begin
        select @mapifailure=1
        break
      end
  
      select @messages=@messages+1
      exec master.dbo.xp_deletemail @current_msg
  
    end /* End of xp_sendmail block. */
  end  /* End of xp_findnextmsg loop. */
  
  /* Finished examining the contents of inbox; 
     now send results. */
  if @mapifailure=1 
    begin
      raiserror(15079,-1,-1,@messages) 
      return(1) 
    end
  else
    return(0) 

End Listing One