SQLMail in Depth

By Sean Baird on 15 September 2000 | Tags: SQLMail

Earlier this week, I published a link to several Microsoft documents that walk you through the installation of SQLMail. I promised a followup to that article, so here ya go.

Steve writes: "How is it possible to have someone send either an email or an attachment(in a specified format)from a standard email program so that the information is entered into a database?

Yep, this is possible, and wow is it cool. Read on for the answer to this question as well as detailed information on SQLMail.

First, a quick primer for those of you that may not be familiar with SQLMail. SQLMail provides an interface from SQL Server to any MAPI-enabled mail client. This interface is a collection of extended stored procedures that allow you to send and read e-mail using Transasct-SQL statements.

Using SQLMail isn't very difficult. I'll cover the most common features of the SQLMail commands here and give some examples. For the full syntax of each extended stored procedure, as well as more examples, check out SQL Server Books Online. As always, if you have more questions, ask us.

Before I get to Steve's question, let's look at sending e-mails with SQLMail, which we get questions on all the time. To send mail, you use the xp_sendmail extended stored procedure. Here's the basic syntax (with the exception of @recipients, all parameters are optional):

xp_sendmail: @recipients='recipient list',; @subject='subject line',; @message='message text',; @attachments='file to attach',; @query='query to execute'

xp_sendmail can be used to send simple notification messages, such as:

xp_sendmail: @recipients='graz@sqlteam.com',; @subject='The Yaks are loose again',; @Message='Summon the Yak Herders!'

It can also be used to send the results of a query:

xp_sendmail: @recipients='graz@sqlteam.com',; @subject='Here are your query results',; @query='SELECT * FROM Yak_Demographics WHERE YakID=42'

In the example above, the results of the query will be included in the e-mail message. Many of the additional parameters for xp_sendmail control how the query is executed (database context, database user) and displayed (headers, formatting, attach query results in a file).

Now, on to Steve's question... There are three procs that you use to manipulate mail in the SQL Server's inbox. In all of these procs, remember that a "Message ID" is simply an arbitrary handle given to the mail message by the SQLMail subsystem. It's a varchar(255).

The first XP we want to look at is xp_findnextmsg, which will return a handle to a single e-mail message in the inbox. It's used to traverse the list of messages in the inbox. The simplified syntax:

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

If @unread_only is set to TRUE, then only unread messages in the inbox will be returned; otherwise, all messages will be returned. If @msg_id is initially NULL, then the handle to the first message in the inbox will be returned. If @msg_id is a valid handle to a message, then the handle to the following message will be returned.

The message handle is returned via @msg_id if it is specified as an OUTPUT parameter, otherwise the message handle is returned as a result set. xp_findnextmsg returns a NULL message handle when there are no more messages to return.

So, the following code snippet will return all message handles in the inbox:


DECLARE @hMessage varchar(255)

EXEC xp_findnextmsg @msg_id=@hMessage OUT
WHILE @hMessage IS NOT NULL
BEGIN
 --put your own message handling code here
 SELECT 'Message handle:' + @hMessage  
 EXEC xp_findnextmsg @msg_id=@hMessage OUT
END

So, what do ya do with those message handles once you have them? Enter xp_readmail:

xp_readmail: @msg_id = 'message handle to read',; @peek = '{true|false}',; @date_received = 'message date' OUTPUT,; @originator = 'sender's friendly name' OUTPUT,; @originator_address = 'sender's resolved address' OUTPUT,; @subject = 'message subject' OUTPUT,; @message = 'message body' OUTPUT,; @recipients = 'recipient list' OUTPUT,; @cc_list = 'carbon copy recipient list' OUTPUT,; @bcc_list = 'blind copy recipient list' OUTPUT,; @unread = '{true|false}' OUTPUT,; @suppress_attach = '{true|false}',; @attachments = 'attachment list' OUTPUT

Wow. Now that's a lot of parameters! I think most of the OUTPUT parameters are self-explanatory; they just return the various attributes of the message in a variable so you can use it. Let's look at some of the not-so-obvious parameters (and check out Books Online for a few I skipped over).

If @peek is set to 'true', then the message won't be marked as read when you read it. Handy for running tests without affecting other processes that check for unread mail.

If @suppress_attach is set to 'true', then SQLMail will not process attachments. Of course, this leads into the question "how does SQLMail process attachments, then?" If @suppress_attach is set to 'false' (it is by default), then SQLMail will save the message attachments into some temporary files and return a semicolon-delimited list of the fully-qualified file names in the @attachments parameter. Parse those out, and do whatever you'd like with the files (BCP them in, for instance).

So, to extend our above example, the following code snippet prints every message in the inbox:


DECLARE @hMessage varchar(255), @MessageText varchar(1000)

EXEC xp_findnextmsg @msg_id=@hMessage OUT
WHILE @hMessage IS NOT NULL
BEGIN
 exec xp_readmail @msg_id=@hMessage, @message=@MessageText OUT
 SELECT 'You''ve got mail! Here it is: ' + @MessageText 
 EXEC xp_findnextmsg @msg_id=@hMessage OUT
END

Admittedly, this isn't very sophisticated, and the AOL reference is probably annoying some of you, but you can quickly see that to parse some e-mail messages, all Steve has to do is use a bit o' code similar to that above.

For another cool feature of xp_readmail, try executing it without any parameters. Bam! You get a resultset that lists every message in your inbox. Save that resultset off to a temporary table, and you can do all sorts of neat stuff.

Finally, if you'd like to delete those annoying spam messages (or any other message, for that matter), simply use:

xp_deletemail 'message ID'

A quick word about sp_processmail. This system stored procedure is supplied by Microsoft, and will process messages in the SQL Server inbox. It assumes the body of the message is a query. For each message in the inbox, it will run the query in the body of the message and return the results of the query (or the errors produced) in an e-mail reply. It's well-documented in Books Online; check it out.

As you may be able to tell, I really like SQLMail :), and I use it for a lot of different things. All of our data warehouse processing starts automatically at midnight, and when it's done, it e-mails me the results of the processing - a summary in the body of the e-mail, and detailed log messages in an attachment. If I enjoyed being woken up in the wee hours of the morning, I could even set up an alert that went to my phone.

I've also set up a custom-made mail processor that runs every few minutes and performs a variety of predefined tasks depending on the subject of the e-mail. I could kick off jobs, return status resports, etc... handy for administering a server over a really slow link.

Now, the most fun I've ever had using SQLMail was when I wrote a timesheet program in MS Excel that kept itself synchronized with a SQL Server via e-mail - the system was designed to be used by traveling consultants that only had access to the corporate office via e-mail.

So, as you can see, SQLMail is pretty flexible, and a novel way of communicating with SQL Server. If any of you are using SQLMail heavily, post your success stories in the forum.

-SQLGuru