SQL Server Reference Guide

Since I've been in the computer industry, I've noticed that there are a few key applications that have stood the test of time. One of the first applications developed even before the Internet was put into commercial service was e-mail. After computers became ubiquitous in business, another major application was the spreadsheet, which is where many people were first introduced to table structures of data. So it stands to reason that applications over time, especially the earlier applications like e-mail and databases would be combined. There are a lot of reasons that you might want to be able to send e-mail from a database system, such as notifying someone that a job had finished or to send the results of a batch job query to a distribution list. But SQL Server adds another feature: you can not only send mail, but SQL Server is able to read the mail and even act on it. The uses for this feature are more impressive than might first appear. The mail you (or your users) send SQL Server can contain a standard query. The query is executed on the server, and the results of the query can be returned to the sender.

You could, for instance, send a query that creates a user in the system from an application. You could also have an Internet-enabled application send an e-mail to SQL Server to order a product. The server can return the status of that order to the user. In this way, SQL Server mail is really just another interface into the system, like ODBC or other connection methods. Additionally, SQL Server can send the results of an automated query to a user or mail group list.

Types of Mail in SQL Server

When you first read about the mail system in SQL Server, you might get a little confused. That’s because there are actually two uses of mail within the system. These two mail subsystems have separate uses, identities, and configurations. Unfortunately, many articles don’t explain this, so you begin to feel a little like the blind men describing the elephant. To make matters even more confusing, SQL Server 2005 adds another type of mail system, called database mail. Today, we’ll take a look at the mail system in SQL Server and clear everything up. There's a lot more than what I'm able to cover in this tutorial, so make sure you check the resources I have at the end of this article.

The mail subsystem used in both SQL Server 2000 and 2005 is called SQL Mail. Once you configure your mail client and the server, SQL Server is able to send and receive mail. I'll show you how to do that setup in a moment. This mail system has some disadvantages, however, since it requires Microsoft Outlook, and works best with a Microsoft Exchange server. This means you have to buy and set up a client on the server where SQL Server is installed. In fact, it's even a bit more onerous than that. I've had the best results for SQL Mail when I used Outlook 2000, which of course is no longer current and has a few security issues.

Another disadvantage with SQL Mail is that it can affect the behavior of SQL Server when it has a problem. It runs "in process," which is computer-speak for working within the same software resources as another process. The upshot is that it could potentially de-stabilize or your freeze your SQL Server if you get into a mail problem. I've had this happen to me from time to time.

But SQL Mail has one very powerful feature that the later version (Database Mail) doesn't: the ability to read mail, not just send it. There are certainly security implications for this feature, but I have used it in the past and found it very useful.

SQL Server 2005 introduces Database Mail, which addresses the stability and security issues, and adds some handy features as well. For one thing, it does not require any mail client software at all, and another advantage is that it runs out of process. In SQL Server 2005, you might have a mail issue, but it won't trouble the server. As I mentioned, it doesn't have the ability to read and process mail, but it is more secure because of it.

Both of the previous examples deal with mail within the database engine. But SQL Server also enables the SQL Server Agent to send mail, which is a good thing since most of the maintenance you'll do uses Agent. When the maintenance fails, succeeds or completes it can send you mail. Not only that, but alerts, jobs and other Agent features can also use e-mail.

Let's take a look at setting up mail in SQL Server and then configure Agent mail. After that we'll explore how you can use e-mail with SQL Server.

Setting up SQL Mail in SQL Server 2000 and 2005

Before you can use e-mail in SQL Server, you need to set up a few things. As I mentioned, SQL Server mail is actually two parts: one for SQL Server, and another for SQL Server Agent. Let's take a look at SQL Mail first, and then Database mail. Both of these can be used for the Agent account, so you don't have to set up anything other than what I'll show you here.

First, locate the accounts that you use to start up both SQL Server and SQL Server Agent. These might be the same, but just open the Control Panel, access the Services icon, and make sure. If the MSSQLServer and SQL Server Agent services are set to use the system account, you’ll need to change that to a regular user account. The system accounts can’t access a mailbox.

Once you find the accounts, make sure that you have a mailbox on your Exchange mail system that these accounts can use. (You can use SMTP, but we’ll cover that later.) Make sure you log on from the server with those accounts and that you can indeed read and send mail with them.

Next, install the e-mail client on the server where SQL Server lives. I’ve found that the Outlook 2000 client is best, but 2003 also works.

Once you have the client software installed, configure an Outlook profile for the account(s) to use. Write this profile name down, and it's actually best if you make it the default. The Outlook documentation will walk you through that setup.

After the client and profile are installed, I normally go into the profile and change a few settings, such as turning off the spell-check, and removing the "Save sent mail in the Sent Items folder" option. If you set up an automated mail process and forget to do this, its size can grow pretty quickly.

Now you just need to set up SQL Server to use that profile. In SQL Server 2000, open Enterprise Manager, then open your server name, and then open Support Services. Right-click SQL Mail and then click Properties. In the Profile name list, select the profile you just created. Click on the Autostart SQL Mail when SQL Server starts check box, and then click OK. Next, right-click SQL Mail and click Start.

While you're here, let's go ahead and set up SQL Server Agent to use mail as well. Still in Enterprise Manager, open the Management object, open SQL Server Agent, right-click it to get the Properties, and set the profile there as well. You’re all set.

For SQL Server 2005, you can follow the same basic procedure. Open the Surface Area Configuration (SAC) tool and allow the feature to run by accessing the Features link. You'll both the SQL Mail and Database Mail checkboxes there. With that done, install the client software and then open SQL Server Management Studio (SSMS) and drill down to the server name, then Management, then Legacy and you'll see SQL Mail. Right-click that object and select Properties from the menu that appears and set your profile up there.

Setting up Database Mail in SQL Server 2005

Setting up database mail is much simpler in SQL Server 2005. You don't need to install a client at all on the server, and you can use SMTP mail with no problem. All you need to make sure is that you can reach the mail server from the SQL Server system.

Before you start, however, you should open the Surface Area Configuration (SAC) tool and allow the feature to run by accessing the Features link. You'll both the SQL Mail and Database Mail checkboxes there.

With that complete, open SQL Server Management Studio (SSMS) and drill down to the server name, then Management, then Database Mail. From there all you have to do is right-click that object and select Configure Database Mail from the menu that appears. It's a very simple process of filling out the information for the server connections and credentials. You'll also notice as you progress through the wizard that you can set up multiple profiles — that will come in handy later.

Using SQL Server Mail (SQL Server 2000 and 2005)

The magic behind all of these operations is a set of stored procedures. These stored procedures start with "xp_", which indicates that they are "eXtended Procedures". That is, they call on programming libraries outside the normal purview of the SQL Server engine. While that doesn’t affect the operation of the stored procedures, it becomes important to know when you’re troubleshooting any problems that arise.

The first stored procedure we’ll look at is xp_sendmail. This command sends mail from SQL Server to an address. Here’s the format:

xp_sendmail
@recipients='Recipients'
, @subject='Subject'
, @message='Message'
, @attachments='Filename'
, @query='SQL Query'

Luckily the syntax is very straightforward, so let's send out a test message to "Buck Woody" on an Exchange server:

xp_sendmail
@recipients='Buck Woody'
, @subject='This is a test'
, @message='This is a test message'

You can try the same thing on your test system; just make sure you change the recipient name to something that is unambiguous. That actually brings up a good point: it's best to use an Exchange "alias" as the recipient, since these are unique, and most of the time doesn't have spaces or other characters in them.

If you got the test message, you can move on to something a little more interesting, such as this example that e-mails the results of a query:

xp_sendmail
@recipients='Buck Woody'
, @subject='This is a test'
, @query=’SELECT au_fname, au_lname, sales FROM authorsales WHERE sales > 50’

This is the easiest use of SQL Server mail, where you’re just sending query results or messages from inside SQL Server. The more-involved part uses several other stored procedures to read mail.

The process isn't really that bad, we just need to access the inbox your server uses to get a "handle" for the mail id, read through the message returned, and then delete the message when it has finished. Let’s take a look at each of these procedures, and then I’ll show you an easier way to use them.

First, we have the xp_findnextmessage stored procedure, which is used to get the handle of the e-mail in question. The format for that command looks like this:

xp_findnextmsg
@msg_id=’message id or returned message id’ [OUTPUT]
, @unread_only=’{true|false}’ 

You need to declare a variable to work with that message id, and then call the stored procedure to read it. Here's an example that creates a variable, and then reads the inbox in a loop, collecting all the handles for the e-mail messages:

DECLARE @hMessage varchar(255)
EXEC xp_findnextmsg @msg_id=@MsgHandle OUT
WHILE @MsgHandle IS NOT NULL
BEGIN
SELECT 
'Handle – ' + @MsgHandle 
EXEC xp_findnextmsg @msg_id=@MsgHandle OUT
END

The @unread_only variable sets whether you want to look through all messages or just the unread ones. If you run that set of statements, you'll have gathered all of the handles of the e-mail

You can have SQL Server read through the mail with xp_readmail. Here's the format of that command:

xp_readmail
@msg_id = ’Handle’
, @peek = ’{true|false}’
, @date_received = ’Message Date’ OUTPUT
, @originator = ’Sender’s name’ OUTPUT
, @originator_address = ’Sender’s address’ OUTPUT
, @subject = ’Subject’ OUTPUT
, @message = ’Message’ OUTPUT
, @recipients = ’Recipients’ OUTPUT
, @cc_list = ’Carbon Copy List’ OUTPUT
, @bcc_list = ’Blind Copy Lally ist’ OUTPUT
, @unread = ’{true|false}’ OUTPUT
, @suppress_attach = ’{true|false}’
@attachments = ’Attachments’ OUTPUT

There’s a lot going on here, but the variables are fairly self-explanatory. The only one that might cause you trouble is "peek;" it leaves the status of the message to "unread," which is useful for testing, or if you are manually reading the e-mails.

When SQL Server is done with the e-mail, it can delete it. The last extended stored procedure we'll look at is xp_deletemail. It’s a bit simpler:

xp_deletemail ’Message ID’

Now, you could implement all these stored procedures to work with e-mail, looping them inside and outside each other, but there’s an easier way. Microsoft created a standard stored procedure that encompasses the previous three that we’ve seen. It gets the next message in the inbox you’ve defined for SQL Server, reads and processes it, and then deletes it. It’s called sp_processmail and the format looks like this:

sp_processmail @subject = ’Subject’ 
 , @filetype = ’filetype’ 
 , @separator = ’separator’ 
 , @set_user = ’user’ 
 , @dbuse = ’dbname’ 

The @filetype variable sets the type of file used (normally txt) to send the results of the query back to the user. It shows up as an attachment to the e-mail message they get. The @separator variable sets the separator character you want to use, the @set_user variable sets the user the query runs under, and the @dbuse variable sets the database for the query, unless it’s specified differently in the query.

One final note about the query the mail contains. It should be a single query only. If you need a multiple-line query, create a stored procedure for the users to access.

Working with SMTP mail servers in SQL Mail

Working with SMTP mail systems are a bit trickier. The reason is that most SMTP systems are poll-based, meaning that they wait for the client to connect for something to happen. Outlook might not connect automatically; the mail may be placed in the outbox, but it doesn’t go anywhere until you log on and open the client. This all has to do with mail spoolers — something I won’t deal with here. The fix? Use Outlook 2000. That’s right — if you’re using a newer mail client, you’ll probably run into issues with SMTP mail.

Using Database Mail (SQL Server 2005 only)

The primary differences in using SQL Mail or Database Mail are that Database Mail can use multiple profiles, but doesn't read or process mail. That makes for a fewer number of stored procedures that you control Database Mail with. You'll notice that I said "stored procedures" and not "extended stored procedures." That's because even though the mail system isn't in-process anymore, the entire engine for it is included in SQL Server.

The primary stored procedure you'll use for Database Mail is sp_send_dbmail. Its syntax is a bit richer than SQL Mail. Here's the whole syntax block from Books Online:

sp_send_dbmail [ [ @profile_name = ] ’profile_name’ ]
  [ , [ @recipients = ] ’recipients [ ; ...n ]’ ]
  [ , [ @copy_recipients = ] ’copy_recipient [ ; ...n ]’ ]
  [ , [ @blind_copy_recipients = ] ’blind_copy_recipient [ ; ...n ]’ ]
  [ , [ @subject = ] ’subject’ ] 
  [ , [ @body = ] ’body’ ] 
  [ , [ @body_format = ] ’body_format’ ]
  [ , [ @importance = ] ’importance’ ]
  [ , [ @sensitivity = ] ’sensitivity’ ]
  [ , [ @file_attachments = ] ’attachment [ ; ...n ]’ ]
  [ , [ @query = ] ’query’ ]
  [ , [ @execute_query_database = ] ’execute_query_database’ ]
  [ , [ @attach_query_result_as_file = ] attach_query_result_as_file ]
  [ , [ @query_attachment_filename = ] query_attachment_filename ]
  [ , [ @query_result_header = ] query_result_header ]
  [ , [ @query_result_width = ] query_result_width ]
  [ , [ @query_result_separator = ] ’query_result_separator’ ]
  [ , [ @exclude_query_output = ] exclude_query_output ]
  [ , [ @append_query_error = ] append_query_error ]
  [ , [ @query_no_truncate = ] query_no_truncate ]
  [ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ]

I won't show you all of the options here, but here's an example of a simple e-mail using Database Mail:

EXEC msdb.dbo.sp_send_dbmail
 @profile_name = ’Default’
, @recipients = ’BuckWoody@somewhere.com’
,  @body = ’This is a test message. ’
,  @subject = ’Test Message from Database Mail’ ;
GO

Here I'm using a profile I made called Default, and I'm sending the e-mail to an SMTP address. If you want to send the body of the e-mail as an HTML message, make sure you set up a variable of the type NVARCHAR MAX, like this:

DECLARE @HTMLVariable NVARCHAR(MAX)

Then you can fill that variable and use it in the @body field.

Having multiple profiles is very useful to be able to send mail from different parts of your applications.

SQL Agent Mail

SQL Agent mail doesn’t need stored procedures to work. The mail it generates is based on events, such as a SQL Database Maintenance Plan or an Alert. When you’re setting these up, the graphical panels set the mail actions.

For the mail to go anywhere, however, you need to set up Operators within SQL Agent. They’re set in the same object (Management) as is the mail profile for SQL Agent. Once you define the Operators, you can assign their mail addresses and they are then available on the graphical panels.

Informit Articles and Sample Chapters

I’ve written another article on SQL Mail, so you can get a different perspective on it here. Also, InformIT is chock-full of articles on SQL Mail, so make sure you search the site for SQL Mail.

Online Resources

For more on setting up the Database Mail wizard, check here.