From xp_sendmail to xp_smtp_sendmail

By Bill Wunder

Earlier, in the article “Take my SQLMail, Please!”, I presented a case for an alternative for SQL mail. Then I described step one of a strategy for moving away from the frailties xp_sendmail by decoupling application code from the actual sending of mail in the article “From xp_sendmail to safe_sendmail”. After this first step is completed we have an environment where there are not hundreds of places in the application that must be changed in order to change how email is processed from the SQL Server. Just like in the movie "What about Bob" we must recognize that this is a baby step and we must move on to the next step. Now I would like to explore the business of replacing the asynchronous call to xp_sendmail with something else. The something else I’ll be exploring is the extended store procedure xp_smtp_sendmail available as a free download from www.sqldev.net, but the general approach can be applied to any alternative most appropriate for a given development shop or solution.

I added some additional parameters to safe_sendmail procedure and to the asynchronous email queue table as presented in to “From xp_sendmail to safe_sendmail” to better exploit the features of xp_smtp_sendmail. First off, I am able to specify the file name when I attach results so I provided a parameter for the developer to name the attachment file. Cool eh? But there’s more! xp_smtp_sendmail supports an email from address, email from name, and even a reply to address. These parameters allow the sender’s email address and display name to be customized and for the reply to be sent to a person or distribution list rather than just getting thrown back into the inbox on the SQL Servers Outlook client when someone hits “Reply to All” for an email sent from the SQL Server. xp_smtp_sendmail even allows me to set the priority of the email, any of high, medium, or low so that the urgency of a mail can be determined upon receipt. All useful features that make an email from the SQL Server better for my environments.

There are actually some capabilities of xp_smtp_sendmail that I do not need so my subsystem does not support them. Others may find these features irresistible and would therefore want to incorporate them into their solution. xp_smtp_sendmail can send multiple attachments, specify an alternate port on the SMTP server, define the codepage for attachments, and even send as html rather than the default plain text format. You’ll want to review the documentation of this or any other SMTP agent you decide to use to help determine the possibilities and which of those possibilities you want to go after for your safe_sendmail.

One thing that xp_smtp_sendmail doesn’t do is execute a query. It has the ability to include a file attachment, even multiple attachments, or it can include the contents of a file up to 64K in size in the body of the mail. That’s half way there but I still needed to get the query results to a file so I can let the extended stored procedure either attach or embed them in an email as appropriate. One truth about human nature motivates me to make this work: It’s much more likely that the recipient will read the body of an email than open an attachment. It is worthwhile to briefly cover how I did this because it highlights the requirements when the subsystem needs some file server storage space where it can generate result files.

The SendAsyncEmail procedure uses a trusted osql call via xp_cmdshell to move the results of a query to a text file. Once the file is created the undocumented xp_getfiledetails extended stored procedure is used to determine the size of the result file – only if @attach_results is not set to true. If the xp_getfiledetails indicate that the file is less than 64K when combined with any @message text specified it will embed the results in the email body. If that size is more than 64K the results are always attached. I find xp_getfiledetails useful in many situations, so I'll show you a chopped down example of how xp_getfiledetails can be used to get information about a query result file. You can use this extended stored procedure to get the same details for any file that the SQL Server can access. 

            -- put it in the body

Another nuance of SendAsyncEmail that may or may not be of interest and use in other environments is it’s built in governor. Seems in my shop, particularly in the development environments, there is an inadvertent tendency to generate hundreds or even thousands of the same email request on occasion. Since I use the same SMTP server for development and production SQL originated email I have included code to avoid bringing the SMTP server to it’s knees when this happens. In fact, I have set the limit at 10 emails. So if SendAsyncEmail sees over 10 pending requests to send an email with the exact same subject to the exact same recipient list the procedure will take those request out of the queue and send a designated default recipient a single email with the subject, recipient list and the count requests attempted. The SMTP server is not swamped and I still have a way to follow up on the requests. I have the SQLAgent job that calls SendAsyncEmail scheduled to run once a minute so the threshold of 10 emails works pretty good for me even though the developers think it should be higher so they can send an email in each iteration of a loop (no way!).

Yet another subtlety of SendAsyncEmail is the use of a table variable to identify which emails will be sent for the current invocation of the procedure. In he more primitive example presented in the last article, the procedure simple set up a loop to get the next email that hadn't been sent and send it until there were no more emails to send. This leaves open the possibility that another process is inserting new rows into the table as fast as they are being sent creating a potential infinite loop that will eventually choke the network. To prevent this, the table variable gets the emails that need to be sent with a single query to AsyncEmailQueue and then processes on that guaranteed to be static list.

I also found that I needed to go to great lengths to accommodate carriage returns, line feeds, tabs and single quotes/apostrophes in the message text in order to keep the developers at least somewhat pleased.

If you’d like to try out this SMTP replacement for SQLMail download and install the .dll containing xp_smtp_email from www.sqldev.net. Be sure to verify that the extended stored procedure is able to successfully send and email through the your SMTP server of choice before proceeding with the safe_sendmail configuration. The easy details on installing and using this extended stored procedure can also be found on the site. I want to re-emphasize: make sure xp_smtp_sendmail is properly working through an SMTP server BEFORE installing the other components presented here.

After you’ve successfully sent a simple test email, create a database named sysmon and compile the scripts for table AsyncEmailQueue and the stored procedures safe_sendmail and SendAsyncEmail. Finally, create a SQL Agent job that calls SendAsyncEmail. In our shop the SQL Agent job runs once a minute. In the job you will need to identify the SMTP server that you will use to send your emails, the UNC or local file system location where you will place attachments, and also specify a default recipient mailbox for all emails as parameters to SendAsyncEmail. Make sure you take steps to prevent the file system location from filling over time. The default recipient helps insure that all email request have someone to send the email to and also provides a recipient for any error handling messages within the procedure.

OK! I’ll give you a few days to get this piece up and running, then I’ll be back with a method for making your SQL Agent job notifications and alerts better by using safe_sendmail rather than the invisible SQLMail interface. See you then.

Bill