/* Script will create the table dbo.AsyncEmailQueue and the procedures dbo.safe_sendmail and dbo.SendAsyncEmail in a pre existing database named sysmon. See my article "Safe Sendmail" for a complete description and discussion of how to implement this SMTP email subsystem from SQL Server 2000 Specifically, note that you need a scheduled job set up calling "SendAsyncEmail" and you need to have xp_smtp_sendmail installed and working */ use sysmon GO CREATE TABLE [dbo].[AsyncEmailQueue] ( [EmailId] [int] IDENTITY (1, 1) NOT NULL , [Datestamp] [datetime] NOT NULL CONSTRAINT [DF_AsyncEmailQueue__DateTime] DEFAULT (getdate()), [SentFlag] [int] NOT NULL CONSTRAINT [DF_AsyncEmailQueue__SentFlag] DEFAULT (0), [Recipients] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [EmailFromName] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [EmailFromAddress] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [CopyRecipients] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [BlindCopyRecipients] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Priority] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Subject] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Message] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Query] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ReplyToAddress] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Attachments] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Type] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [NoHeader] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Width] [int] NULL , [Separator] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [EchoError] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [DBuse] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [AttachResultsFileName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [AttachResults] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , CONSTRAINT [pk_AsycnEmailQueue__EmailId] PRIMARY KEY CLUSTERED ( [EmailId] ) ON [PRIMARY] ) ON [PRIMARY] GO use sysmon GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[safe_sendmail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[safe_sendmail] 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 * * Project: SendAsyncEmail - replaces xp_sendmail * * Description: replacement for xp_sendmail to allow asynchronous email delivery. * * Usage: exec sysmon.dbo.safe_sendmail @recipients = 'DBA@yourdomain.com' , @subject = 'test1' , @message = 'An example of a simple safe_sendmail call' exec sysmon.dbo.safe_sendmail @recipients = 'DBA@yourdomain.com' , @subject = 'test2' , @message = 'An example of a safe_sendmail call that will send the query result in the body' , @query = 'select top 10 au_fname, au_lname from pubs.dbo.authors' exec sysmon.dbo.safe_sendmail @recipients = 'DBA@yourdomain.com' , @subject = 'test3' , @message = 'An example of a safe_sendmail call that will send the query result as an attachment' , @query = 'select text from northwind.dbo.syscomments' * * * Modifications: * Developer Name Date Brief Description * ------------------ -------- ------------------------------------------------------------ * **********************************************************************/ 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 use sysmon GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SendAsyncEmail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[SendAsyncEmail] GO CREATE PROCEDURE [dbo].[SendAsyncEmail] @ResultPath varchar(255) , @MailHost varchar(255) , @DefaultRecipient varchar(50) AS /******************************************************************************************************* * sysmon.dbo.SendAsyncEmail * * Project: SendAsyncEmail - replaces xp_sendmail * * Description: SMTP replacement for xp_sendmail. * Notes: SentFlag values used include * 0 - ready to send * 1 - being sent * 2 - sent * 3 - cancelled * any negative number - failed to send * * Usage: exec sysmon dbo.SendAsyncEmail @ResultPath = 'C:\temp' -- or '\\\' , @MailHost = 'MAILSERVER.yourdomain.com' , @DefaultRecipient = 'DBA@yourdomain.com' * * * Modifications: * Developer Name Date Brief Description * ------------------ -------- ------------------------------------------------------------ ********************************************************************************************************/ --------------------------------------------- -- declare variables --------------------------------------------- declare @EmailId int , @rc int , @ec int , @Recipients varchar(512) , @Message varchar(2000) , @Query varchar(1000) , @CCRecipients varchar(255) , @BCCRecipients varchar(255) , @Subject varchar(255) , @NoHeader varchar(5) , @Width int , @Separator varchar(255) , @DBUse varchar(255) , @SQLStr varchar(8000) , @ResultFile varchar(255) , @DateSend datetime , @EmailFromAddress varchar(50) , @EmailToAddress varchar(128) , @AttachResultsFile varchar(255) , @EmailFromName varchar(75) , @Priority varchar(6) , @Quote char(1) , @CrLf char(2) , @ReplyToAddress varchar(100) , @MessageFile varchar(255) , @Attachments varchar(2000) , @AttachResults varchar(5) , @RecipientWithTooMany varchar(512) , @SubjectWithTooMany varchar(255) , @EmailsToSend int , @DefaultFromAddress varchar(50) --------------------------------------------- -- create temp tables --------------------------------------------- -- create a temporary table to store file details. This is used to determine query results > 64000 create table #filedetails (AlternateName varchar(33) , Size int , CreationDate int , CreationTime int , LastWrittenDate int , LastWrittenTime int , LastAccessedDate int , LastAccessedTime int , Attributes int) declare @worklist table (EmailId int primary key) --------------------------------------------- -- set session --------------------------------------------- SET NOCOUNT ON set @Quote = char(39) set @DateSend = getdate() set @CrLf = char(13) + char(10) -- instance name pluse same domaina as default recipient set @DefaultFromAddress = replace(@@servername,'\','$') + substring(@DefaultRecipient,charindex('@',@DefaultRecipient),datalength(@DefaultRecipient)) --------------------------------------------- -- body of stored procedure --------------------------------------------- -- remove old record once a day (keep 60 days) if datepart(minute, getdate()) = 1 and datepart(hour, getdate()) = 1 delete sysmon.dbo.AsyncEmailQueue where Datestamp < getdate() - 60 and SentFlag = 2 -- to prevent a race condition where a job is adding multiple request while -- this proc is processing them always work from a snapshot of emails to send -- more request for the same recipient and subject, insert @worklist select EmailId from sysmon.dbo.AsyncEmailQueue where SentFlag = 0 set @EmailsToSend = @@rowcount while @EmailsToSend > 10 begin -- Make sure the queue is not loaded with many requests for the same email select @EmailsToSend = max(x.[count]), @RecipientWithTooMany = x.Recipients, @SubjectWithTooMany = x.Subject from (select top 1 count(q.EmailId) [count], Recipients, Subject from sysmon.dbo.AsyncEmailQueue q join @worklist w on q.EmailId = w.EmailId where q.SentFlag = 0 group by q.Recipients, q.Subject order by count(q.EmailId) desc) x group by x.Recipients, x.Subject if @@rowcount > 0 and @EmailsToSend > 10 begin -- cancel update sysmon.dbo.AsyncEmailQueue set SentFlag = 3 where Recipients = @RecipientWithTooMany and Subject = @SubjectWithTooMany and SentFlag = 0 --remove from the worklist delete w from sysmon.dbo.AsyncEmailQueue q join @worklist w on q.EmailId = w.EmailId where q.Recipients = @RecipientWithTooMany and q.Subject = @SubjectWithTooMany and q.SentFlag = 3 -- tell somebody about the spammer Set @Message = 'The server has ' + cast(@EmailsToSend as varchar(10)) + + ' email request with subject ' + @CrLf + space(5) + @SubjectWithTooMany + @CrLf + 'queued to send to recipient(s) ''' + @RecipientWithTooMany + ''' . These messages will not ' + 'be sent and are now set to status 3 (canceled) in sysmon.dbo.AsyncEmailQueue to ' + 'prevent excess load on the mail delivery system.' exec @rc = master.dbo.xp_smtp_sendmail @from = @DefaultFromAddress , @from_name = @@servername , @to = @DefaultRecipient , @replyto = @DefaultRecipient , @priority = 'high' , @subject = 'Excessive requests to send the same email.' , @message = @Message , @server = @MailHost set @ec = @@error if @rc <> 0 or @ec <> 0 goto ErrorHandler end else begin -- Get a new count to move to the main loop select @EmailsToSend = count(EmailId) from @worklist break end end -- loop through the waiting emails if any exist while @EmailsToSend > 0 begin -- init variables for this iteration set @MessageFile = null -- pick up the ID of the first email waiting select top 1 @EmailId = q.EmailId, @Recipients = replace(isnull(q.Recipients,@DefaultRecipient), @CrLf, space(1)) , @CCRecipients = replace(q.CopyRecipients, @CrLf, space(1)) , @BCCRecipients = replace(q.BlindCopyRecipients, @CrLf, space(1)) , @Message = isnull(q.Message,'') , @Query = replace(isnull(q.Query,''), @CrLf, space(1)) , @Subject = isnull(q.Subject, 'SQL Server Message') , @NoHeader = isnull(q.NoHeader,'false') , @Width = isnull(q.Width,80) , @Separator = isnull(q.Separator,space(1)) , @DBUse = isnull(q.DBUse,'tempdb') , @AttachResultsFile = isnull(q.AttachResultsFileName,'') , @EmailFromAddress = isnull(q.EmailFromAddress, @DefaultFromAddress) , @EmailFromName = isnull(q.EmailFromName, replace(@@servername,'\','$')) , @Priority = isnull(q.Priority,'normal') , @ReplyToAddress = isnull(q.ReplyToAddress,@DefaultRecipient) , @Attachments = replace(isnull(q.Attachments,''),',',';') , @AttachResults = isnull(q.AttachResults,'false') from sysmon.dbo.AsyncEmailQueue q join @worklist w on q.EmailId = w.EmailId where q.SentFlag = 0 -- set the flag (1 = attempting to send) update sysmon.dbo.AsyncEmailQueue set SentFlag = 1 where EmailId = @EmailId -- if there is a query, execute the proc and place results in result file. if @Query <> '' begin -- prefix the resultfile content with the message -- this will cause redundant delivery of message text -- but think this is better than creating two files when result set > 64K -- skip this concatenation if user explicitly wants result attached if @Message <> '' and @AttachResults <> 'true' set @Query = 'print' + space(1) + @Quote + replace(replace(replace(replace(@Message , @Quote , @Quote + @Quote) , char(13) , @Quote + ' + char(13) + ' + @Quote) , char(10) , @Quote + ' + char(10) + ' + @Quote) , char(9) , @Quote + ' + char(9) + ' + @Quote) + @Quote + ' + char(13) + char(10) + char(13) + char(10)' + space(1) + @Query -- generate a query result file at this location and name if (substring(@ResultPath, datalength(@ResultPath), 1) <> '\') set @ResultPath = @ResultPath + '\' select @ResultFile = @ResultPath + case when (@AttachResultsFile <> '') then case when charindex('.',@AttachResultsFile) > 0 -- has extension then @AttachResultsFile else @AttachResultsFile + '.txt' end else replace(@@servername,'\','$') + cast(@EmailId as varchar(10)) + '.txt' end --build on osql string to move the query results to a file select @SQLStr = 'osql -S' + @@servername + ' -E -d' + @DBUse + ' -t120 ' + case when @NoHeader = 'true' then '-h-1 ' else ' ' end + '-s"' + @Separator + '" ' + '-w' + cast(@Width as varchar(10)) + ' ' + '-Q"' + @Query + '" -n -m-1 -o"' + @ResultFile + '" -b' exec @rc = master.dbo.xp_cmdshell @SQLStr set @ec = @@error -- don't fail the send on a bad return code -- could be a bad query and the error should be passed along -- if the output file is not produced will fail in next step if @ec <> 0 goto ErrorHandler insert #filedetails exec @rc = master.dbo.xp_getfiledetails @ResultFile set @ec = @@error if @rc <> 0 or @ec <> 0 goto ErrorHandler --execute xp_smtp_sendmail..based on file size either use attachment or messagefile if (select Size from #filedetails) > 64000 or @AttachResults = 'true' begin if (select Size from #filedetails) > 2000000 begin Print 'EmailId ' + cast(@EmailId as varchar(10)) + + ' requested a result set that contains ' + 'more than 2MB of data. The result set was not ' + 'attached to prevent excess load on the mail delivery ' + 'system.' GoTo ErrorHandler end else if @Attachments = '' set @Attachments = @ResultFile else set @Attachments = @Attachments + ';' + @ResultFile end else begin set @Message = null set @MessageFile = @ResultFile end end -- there is a query to run exec @rc = master.dbo.xp_smtp_sendmail @from = @EmailFromAddress , @from_name = @EmailFromName , @to = @Recipients , @replyto = @ReplyToAddress , @cc = @CCRecipients , @bcc = @BCCRecipients , @priority = @Priority , @subject = @Subject , @message = @Message , @attachments = @Attachments , @messagefile = @Messagefile , @server = @MailHost set @ec = @@error if @rc <> 0 or @ec <> 0 goto ErrorHandler -- mark this email as sent update sysmon.dbo.AsyncEmailQueue set SentFlag = 2 where EmailId = @EmailId -- clear temp table truncate table #filedetails set @EmailsToSend = @EmailsToSend - 1 end --end while return ErrorHandler: -- Sent flag should always be less than 0 if failed to send update sysmon.dbo.AsyncEmailQueue set SentFlag = case when @rc = 0 then -99 else -@rc end where EmailId = @EmailId Raiserror ('sysmon.dbo.SendAsyncEmail failed to send EmailId %d, return code: %d, error: %d ',16,1,@EmailId, @rc, @ec) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO