Skip Navigation LinksHome > Categories > Code from a Category

What is SQLMail

User Name: codelecturer
Name: Mike Chauhan
Contact Me:
Home Page:
6 years of total IT experience including programming, application development, System Maintenance . 3 years of Experienced and expertise in .Net Framework VB, Visual Basic.Net, ASP.Net 2.0, 3.5 and AD... [More]
Viewed Times: 2075
Add Date: 12/15/2013
In this article we will learn about SQLMail.
"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.

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 can be used to send simple notification messages, such as:

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).

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:

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:

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

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.

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:

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:

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.

Happy Programming!!!

Post a Comment

Name: (Optional)
Email: (Optional, you can get an email if somebody replys your comments)*
Email me if somebody respons my comment below:
Enter Text
as Below:
(case insensitive, if hard to read, click the "get a new one" button)
* Your email address will not be shared with any third parties for any reason.
** Maximum 1000 charactors.