[MSSQL]如何用SQLServer自动发送邮件

通常我们用SQL Server进行定期的大量计算后，都希望能收到一些通知形的Email告诉我们一些简单信息，如操作是否开始了？操作是否进行中？计算结果是多少？操作过程中是否有错误？如果希望得到更加详细的信息，类似与进度条之类的消息，如此次操作一共有多少步骤？现在进行到了第几步？上一步是否正常完成？上一步计算了多少条数据，计算结果为多少？这样每一步我们都能收到Email，就可以靠Email信息来判断操作是否成功，而不需要自己去数据库了比较验证操作是否完成，是否正确执行了。

那么如何让SQLServer实现这样的功能呢？原理我们就不讲了，直接讲步骤。

1. 首先建立一个新的数据库MailDB（当然，你已有的数据库也可以。新数据库只是为了方便讲解），在MailDB里建立一个表Mail_GenerateMailRequest，专门用来存放Email的信息。这个表结构如下：

建立此表的Script如下：

 USE [MailDB]

 GO 

 SET ANSI_NULLS ON 

 GO 

 SET QUOTED_IDENTIFIER ON 

 GO 

 SET ANSI_PADDING ON 

 GO 

 CREATE TABLE [dbo].[Mail_GeneralMailRequest](

 [RequestID] [bigint] IDENTITY(1,1) NOT NULL,

 [RequestTime] [datetime] NOT NULL,

 [MailRecipients] [varchar](50) NOT NULL,

 [MailCC] [varchar](50) NULL,

 [MailBCC] [varchar](50) NULL,

 [MailType] [varchar](50) NOT NULL,

 [MailTitle] [varchar](100) NOT NULL,

 [MailContent] [varchar](2000) NOT NULL,

 [MailAttachment] [varchar](100) NULL,

 [MailTime] [datetime] NULL,

 [MailQuery] [varchar](1000) NULL 

 ) ON [PRIMARY]

 GO 

 SET ANSI_PADDING OFF 

 GO 

这个表用来存放要发送Email的信息，也就是每一条记录都应该要发送一封Email出去。显然，我们这里需要用到trigger来触发这个动作。每当有新纪录插入时，就发送Email。

这个trigger的内容如下：

 USE [MailDB]

 GO 

 SET ANSI_NULLS ON 

 GO 

 SET QUOTED_IDENTIFIER ON 

 GO 

 CREATE trigger [dbo].[tr_Mail_GeneralMailRequest] on [dbo].[Mail_GeneralMailRequest]

 for insert

 as 

 declare @requestID bigint

 select @requestID = RequestID

 from inserted

 execute SYS_Mail_GeneralMailRequest @requestID

看了上面的trigger后，很显然重点在SYS_Mail_GeneralMailRequest。它才是真真正正用来发送Email的东西。我们来看看这个存储过程是怎样的。

 USE [MailDB]

 GO 

 SET ANSI_NULLS ON 

 GO 

 SET QUOTED_IDENTIFIER ON 

 GO 

 ALTER PROCEDURE [dbo].[SYS_Mail_GeneralMailRequest]

 @requestID bigint

 as 

 set xact_abort on 

 set nocount on 

 declare @requestTime datetime

 declare @mailRecipients varchar(50)

 declare @mailCC varchar(1000)

 declare @mailBCC varchar(100)

 declare @mailType varchar(50)

 declare @mailTitle varchar(50)

 declare @mailContent varchar(1000)

 declare @mailAttachment varchar(1000)

 declare @mailquery varchar(1000)

 declare @result int 

 select @requestTime = RequestTime

 , @mailRecipients = MailRecipients, @mailCC = MailCC, @mailBCC = MailBCC

 , @mailType = MailType, @mailTitle = MailTitle, @mailContent = MailContent

 , @mailAttachment = MailAttachment,@MailQuery=mailquery

 from Mail_GeneralMailRequest

 where RequestID = @requestID

 if @mailtype='Query' 

 begin 

 EXEC master.dbo.xp_sendmail @recipients = @mailRecipients,

 @query = @MailQuery,

 @subject = @mailTitle,

 @message = @mailContent,

 @attach_results = 'FALSE', @width = 7000

 end 

 else 

 begin 

 execute                                                  

 , @message = @mailContent, @attachments = @mailAttachment

 , @copy_recipients = @mailCC, @blind_copy_recipients = @mailBCC

 , @subject = @mailTitle

 end 

 update Mail_GeneralMailRequest

 set MailTime = getdate()

 where RequestID = @requestID

这样我们就可以用SQL Server发送Email了。在需要发送Email的地方，往Mail_GenerateMailRequest表里插入数据。如下：

 INSERT INTO Mail_GenerateMailRequest(

 [RequestTime],

 [MailRecipients], [MailCC], [MailBCC], [MailType],

 [MailTitle],

 [MailContent],

 [MailAttachment]

 )

 VALUES (

 GETDATE(),

 'test@126.com', '', NULL, 'DBMail',

 CASE @Success WHEN 0 THEN 'ERR ' ELSE '' END + 'Test Result:' + convert(varchar,getdate(), 111) + ',' + @ProcessName,

 CASE @Success WHEN 0 THEN 'ERR ' ELSE '' END + 'Test Result:' + convert(varchar,getdate(), 111) + ',' + @message,

 NULL 

 )

这样就大功告成了。