From xp_sendmail to safe

From xp_sendmail to safe_sendmail

By Bill Wunder

In the previous column, “Take my SQLMail, Please!”, I presented a case for an alternative for SQL mail. I’m going to continue on the premise that you found the argument at least somewhat compelling and dive right in to a discussion of one possible alternative. As always, you may see things that don’t suit your needs. Feel free to take what ever you see here that may be useful and add to it or change it as you see fit to make your SQL Server a better one.

Foremost in moving from SQLMail, is to keep the switch as easy and painless as possible. To accomplish this, first consider moving away from xp_sendmail and then you will have a foundation upon which to move the SQLAgent’s SQLMail capabilities.

Each existing application will have some level of usage of xp_sendmail and any replacement subsystem must minimize sweeping changes necessary to what already exists to assure a clean transition. At the same time, it would make sense to decouple the sending of email and the execution of all application stored procedures and scripts. Not only will this serve to prevent the application from breaking when the mail subsystem breaks as happens with xp_sendmail, it will also allow changes the underlying email architecture at will without having to make a pass through all existing T-SQL with each revision in the future, provide an opportunity to apply system wide rules to all SQL Server originated email, and generate a sent items history easily accessible inside SQL Server.

These design goals can be easily met by creating a stored procedure that accepts all the parameters of xp_sendmail and inserting those parameters into a table that will act as a queue. All we need do to convert applications to the new architecture is replace xp_sendmail with this new stored procedure name. From the table, any process or procedure – even xp_sendmail - can easily pick up rows and send emails asynchronously from the application stored procedures. For this discussion mail will be processed from the SQL Agent by a stored procedure name SendAsyncEmail.

Get procedures/triggers that include a reference to xp_sendmail

exec sp_MSforeachdb 'use ?

Select db_name()

, object_name(id)

from syscomments

where text like ''%xp_sendmail%'''

or if you want to generate a script so you can review each reference

exec sp_MSforeachdb 'use ?

Select ''use '' + db_name() +

''exec sp_helptext '' + object_name(id)

from syscomments

where text like ''%xp_sendmail%'''

Of course the table should also include a column to indicate that a mail needs to be, has been, or even won’t be sent as well as columns to indicate who requested the send, when, and any features that I want to add to my replacement subsystem. The basic table structure is something like:

use sysmon

CREATE TABLE [dbo].[AsyncEmailQueue] (

[EmailId] [int] IDENTITY (1, 1) NOT NULL

, [RecCreatedDt] [datetime] NOT NULL

CONSTRAINT [DF_AsyncEmailQueue__DateTime]

DEFAULT (getdate())

, [SentFlag] [int] NOT NULL

CONSTRAINT [DF_AsyncEmailQueue__SentFlag]

DEFAULT (0)

, [Recipients] [varchar] (512) NULL

, [Message] [varchar] (2000) NULL

, [Query] [varchar] (1000) NULL

, [Attachments] [varchar] (2000) NULL

, [CopyRecipients] [varchar] (255) NULL

, [BlindCopyRecipients] [varchar] (255) NULL

, [Subject] [varchar] (255) NULL

, [AttachResults] [varchar] (5) NULL

, [NoHeader] [varchar] (5) NULL

, [Width] [int] NULL

, [Separator] [varchar] (255) NULL

, [DBuse] [varchar] (255) NULL

, CONSTRAINT [pk_AsycnEmailQueue__EmailId]

PRIMARY KEY CLUSTERED

(

[EmailId]

) ON [PRIMARY]

I deviate from my usual modus operandi of always building tools in the admin database (see my article http://www.sswug.org/see/14791 T-SQL Tool House) and build this table and the supporting procedures in another database because I want this tool to be used by anyone interested. This is very different from the data and stored procedures of the admin database which I am not so keen on opening up to the general developer population. I’ll put this new email subsystem in a database named sysmon because I want the developers to use email notification within SQL Server to help them track, monitor and respond to events and conditions within the data set. Sysmon then is an acronym for system monitoring.

The procedure that will replace xp_sendmail I’ll call safe_sendmail. I could just replace each call to xp_sendmail with a call to sysmon.dbo.safe_sendmail and be done with it, but I’m going to take it a couple of steps farther. I’ll set a standard that requires an explicit name for each parameter. That sets up a little more work for me during the conversion, but will make the new mail subsystem clear in purpose and more self documenting moving forward. One more thing I will do is convert any recipient names to be valid SMTP email addresses. That’s right, no more Address Book Display names.

Note: You can use the Address book to determine the SMTP name for any recipient by looking at the “Email Addresses” tab of the Properties for a user. It’s even possible that your organization is still stuck in the dark ages of Exchange Server and you’ll need to motivate a sleepy mail server admin to add SMTP addresses for all users. Trust me, the Exchange 2000 documentation and mail server best practices have been telling them to do so for quite some time.

To illustrate the changes necessary for each call consider this basic example:

Before:

Exec master.dbo.xp_sendmail

'Bill Wunder'

, 'This is a test'

, 'Just testing, please ignore'

After:

Exec sysmon.dbo.safe_sendmail

@recipients = 'bill.wunder@wallst.com'

, @subject = 'This is a test'

, @mesage = 'Just testing, please ignore'

There are parameters to xp_sendmail that will not be fully supported by this asynchronous subsystem. I find they are seldom used and even less often needed. The safe_sendmail stored procedure will accept them if specified, I just won’t do anything with them when I read the table later. The parameters @no_output, and @echo_error no longer have meaning in an asynchronous mail delivery model. The @type parameter is MAPI specific. @set_user may be of value in an environment where column level permissions are set or security around sensitive data is an issue. Since the SQLAgent will be executing all queries submitted by safe_sendmail, all permissions will be in the context of the SQLAgent service account. Usually this service account is a member of the sysadmins server role so no query is restricted by permissions. This is not an important consideration in our environment so I did not incorporate @set_user into this solution. Certainly anyone that found value in these parameters could include the xp_sendmail behavior if necessary and desirable. See the xp_sendmail documentation in SQL Server Books on Line for a full description of these parameters. Use your creative skills to implement them as necessary for your environment. Here’s what I’d suggest as a starting place for the safe_sendmail procedure:

use sysmon

SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON

CREATE PROC dbo.safe_sendmail

@recipients varchar(512)

, @message varchar(2000) = null

, @query varchar(1000) = null

, @attachments varchar(2000) = null

, @copy_recipients varchar(255) = null

, @blind_copy_recipients varchar(255) = null

, @subject varchar(255) = 'SQL Server Message'

, @attach_results varchar(5) = null

, @no_header varchar(5) = null

, @width int = null

, @separator varchar(255) = null

, @echo_error varchar(5) = null

, @no_output varchar(5) = null

, @set_user varchar(255) = null

, @type varchar(255) = null

, @dbuse varchar(255) = null

/***********************************************************************

* sysmon.dbo.safe_sendmail

* replacement for xp_sendmail to allow asynchronous email delivery.

**********************************************************************/

set nocount on

declare @send_Date datetime

-- warn about unused parameters if provided

if @echo_error is not null

print '@echo_error is not supported'

if @no_output is not null

print '@no_output is not supported'

if @set_user is not null

print '@set_user is not supported'

if @type is not null

print '@type is not supported'

-- insert into the asyncronous table

insert into sysmon.dbo.AsyncEmailQueue

(recipients

, Message

, Query

, Attachments

, CopyRecipients

, BlindCopyRecipients

, Subject

, AttachResults

, NoHeader

, Width

, Separator

, DBuse)

values (@recipients

, @message

, @query

, @attachments

, @copy_recipients

, @blind_copy_recipients

, @subject

, @attach_results

, @no_header

, @width

, @separator

, @dbuse)

SET QUOTED_IDENTIFIER OFF

SET ANSI_NULLS ON

You’ll need to make sure every database user that should have the ability to send emails has adequate permissions to access the safe_sendmail stored procedure. Much better than having to extend execute permissions for xp_sendmail to users other than sysadmins. In fact, check out what Books on Line has to say about permissions for xp_sendmail.

Now all you need is a stored procedure that will select from the table and use the parameters to execute xp_sendmail and you will have an asynchronous email subsystem. Something like:

use sysmon

SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON

CREATE PROC dbo.SendAsyncEmail

/***********************************************************************

* sysmon.dbo.SendAsyncEmail

* call xp_sendmail from SQLAgent job

**********************************************************************/

set nocount on

declare @Recipients varchar(512)

, @Message varchar(2000)

, @Query varchar(1000)

, @Attachments varchar(2000)

, @CopyRecipients varchar(255)

, @BlindCopyRecipients varchar(255)

, @Subject varchar(255)

, @AttachResults varchar(5)

, @NoHeader varchar(5)

, @Width int

, @Separator varchar(255)

, @NoUutput varchar(5)

, @DBUse varchar(255)

while exists (select EmailId

from sysmon.dbo.AsyncEmailQueue

where SentFlag = 0)

begin

select top 1 @Recipients = Recipients

, @Message = Message

, @Query = Query

, @Attachments = Attachments

, @CopyRecipients = CopyRecipients

, @BlindCopyRecipients = BlindCopyRecipients

, @Subject = Subject

, @AttachResults = AttachResults

, @NoHeader = NoHeader

, @Width = Width

, @Separator = Separator

, @DBUse = DBUse

from sysmon.dbo.AsyncEmailQueue

where SentFlag = 0

exec master.dbo.xp_sendmail

@recipients = @Recipients

, @message = @Message

, @query = @Query

, @attachments = @Attachments

, @copy_recipients = @CopyRecipients

, @blind_copy_recipients = @BlindCopyRecipients

, @subject = @Subject

, @attach_results = @AttachResults

, @no_header = @NoHeader

, @width = @Width

, @separator = @Separator

, @dbuse = @DBUse

end

SET QUOTED_IDENTIFIER OFF

SET ANSI_NULLS ON

All that’s left is to add a SQL Agent job that calls the SendAsyncEmail stored procedure at what ever frequency you deem correct. Once you have fully implemented the changes suggested in here you will have achieved two important milestones. First, sending an email will no longer cause any of your application code to fail or hang because of problems with SQL Server’s MAPI interface or your mail client or even the Exchange Server. Second, you will have put yourself in a position so that you can consider any email solution you want simply by changing what happens in the SendAsyncEmail procedure. How you send email from SQL Server will be independent from your application.

For my subsystem, the SendAsyncEmail stored procedure is a wrapper for the very cool xp_smtp_sendmail extended stored procedure available for free download at www.sqldev.net. I don’t think you’ll find an SMTP relay agent better suited to SQL Server. In the next article I’ll discuss how I have extended the technology presented in this article to incorporate this extended stored procedure into the backend component of my email subsystem. Hope to see you there.

Bill