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

GO

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]

) 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

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

 

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

as

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

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

     

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

 

 

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

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

 

CREATE PROC dbo.SendAsyncEmail 

as

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

*  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

     

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

 

 

 

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