sqlserver2008自动发送邮件

实现一个类似于注册平台的功能：比如注册了一个用户，就会向注册邮箱里发送一封邮件。首先是要搭建一个自动发送邮件的平台，这个用sql server 2008（sql server 2005也有）的database mail就能很方便的实现。这个完成了以后就能够发送简单的邮件了，但是这样在应用中往往是不够，我们还要需要建一个job来实现自动发送的功能。
database mail的构建有两种方法，一是用configure database mail，另外一种是用sql语句完成。
这两种方法中的服务器名字，我刚开始以为是随便起一个就可以的，（以为sql server的database mail会自动把这个服务器名申请为一个可用的smtp服务器）后来貌似不是的。这里好像必须要是一个存在并且可用的smtp服务器名
如果想要删除建立好的profile和account可以用如下语句

 

复制代码代码如下:
DECLARE 
@account_name sysname, 
@profile_name sysname; 
SELECT 
@account_name = N'want_to_delete_Account' 
@profile_name = N'want_to_delete_Profile'; 

-- 从邮件配置文件中删除数据库邮件帐号 
EXEC msdb.dbo.sysmail_delete_profileaccount_sp 
@profile_name = @profile_name, 
  @account_name = @account_name; 

-- 删除数据库邮件帐号 
EXEC msdb.dbo.sysmail_delete_account_sp 
@account_name = @account_name; 

-- 删除数据库邮件配置文件 
EXEC msdb.dbo.sysmail_delete_profile_sp 
@profile_name = @profile_name; 

 

完成了database mail的搭建。下面我们就要开始完善自动发送邮件的功能。
我们要实现发送邮件的格式是：
尊敬的×××：---------------------------------------------titleName
感谢您的注册，你的用户名是××，密码是××--------------------------object
你的满意是我们最大的动力。----------------------------------------------------Conclusion
某某公司             ----------------------------------------------------------Inscribe
首先我们可以建两个表：一个是AutoSendEmail记录用户ID和Email的信息；一个是EmailText，一个static table，里面存储的是我们要发送的邮件的格式。
AutoSendEmail的主要字段有UserID，EmailAddress，EmailTextID（EmailText的一个外键）。这里的UserID对应的是一个User表，里面存有用户的登录名及登录密码。
EmailText的主要字段有EmailTextID，Subject（主题），titleName（称呼），object（内容），Conclusion（结束语），Inscribe（落款）。可能看到这里，有人会认为EmailText表没必要设计的这么麻烦。有EmailTextID，Subject和一个body就够了，确实，但是这样的话，内容中的排版格式比较麻烦。这样设计一是为了格式方便，一个是为了和发送其他邮件格式统一。插入一行数据到EmailText表中
insert into EmailText (EmailTextID,Subject,TitleName,Object,Conclusion,Inscribe) values(1,'注册成功','尊敬的<UserName>:','
 感谢您申请注册某某公司会员!  你的注册帐号为<LoginName>，密码为<Password>，请妥善保管您的密码！','
 感谢您对我们公司的支持！如果任何意见或建议，欢迎来电咨询:123-456789。请不要回复此邮件！',' 某某公司')
 这样，用job每次遍历AutoSendEmail，从中得到UserID和EmailTextID，然后根据UserID可以得到相应的登录名和登录密码。同时根据EmailTextID，取出里面的titleName（称呼），object（内容），Conclusion（结束语），Inscribe（落款）四个字段，用得到的登录名和登录密码去替换<UserName> ,<LoginName>,<Password>这样就可以拼凑成一封邮件的完整body。
为此，先建立一个procedure

 

复制代码代码如下:
CREATE PROCEDURE [Services].[SendRegisteredEmail] 
(  
    @UserID int, 
    @EmailAddress nvarchar(100) 
--  @mailitem_id int OUTPUT 
 ) 
AS 
declare @userName nvarchar(30) 
declare @Login nvarchar(50) 
declare @password nvarchar(50) 
declare @regUserID int 
declare @titleName nvarchar(100) 
declare @titleResult nvarchar(100) 
declare @object nvarchar(600) 
declare @objectResult nvarchar(600) 
declare @Subject1 nvarchar(100) 
declare @Conclusion nvarchar(600) 
declare @Inscrible nvarchar(100) 
declare @result int 
--declare @time datetime 
declare @body1 nvarchar(max) 
begin 
select @userName=LastName from [Users].[User] where UserID=@UserID 
select @Subject1=Subject,@titleName=TitleName ,@object=Object,@Conclusion=Conclusion,@Inscrible=Inscribe from [Services].[EmailText] where EmailTypeID=1 
select @titleResult=replace(@titleName,'<UserName>',@userName) 
select @Login=Login ,@password=Password from [Users].[RegisteredUser] where UserID=@UserID 
select @objectResult=replace(@object,'<LoginName>',@Login) 
select @objectResult=replace(@objectResult,'<LoginName>',@Login) 
set @body1=@titleResult+N'<br>'+@objectResult+N'<br>'+@Conclusion+N'<br>'+@Inscrible 
EXEC @result=msdb.dbo.sp_send_dbmail @profile_name='SurmountGroupProfile', 
                             @recipients=@EmailAddress, 
                             @subject= @Subject1, 
                             @body = @body1, 
                             @body_format = 'HTML'; 
 return @result 

end 

 

上面代码用了一个sql里的替换函数replace，该函数的用法是
用第三个表达式替换第一个字符串表达式中出现的所有第二个给定字符串表达式。
语法 REPLACE ( 'string_expression1' , 'string_expression2' , 'string_expression3' )
参数 'string_expression1' 待搜索的字符串表达式。string_expression1 可以是字符数据或二进制数据。
'string_expression2' 待查找的字符串表达式。string_expression2 可以是字符数据或二进制数据。
'string_expression3' 替换用的字符串表达式。string_expression3 可以是字符数据或二进制数据。
返回类型
如果 string_expression（1、2 或 3）是支持的字符数据类型之一，则返回字符数据。如果 string_expression（1、2 或 3）是支持的 binary 数据类型之一，则返回二进制数据。
  msdb.dbo.sp_send_dbmail存储过程是用于执行发送邮件的，返回值为0或是1。0代表发送成功，1为发送失败
邮件的格式是用HTML形式显示
有了这个存储过程，那么只要用个建个job就可以了。job主要是为了实现某些需要数据库自动处理的功能。
job里的command输入如下代码：

 

复制代码代码如下:
declare @recordID int 
declare @userID int 
declare @email nvarchar(100) 
declare @typeID int 
declare @result int 
declare EmailCursor cursor for select RecordID,UserID,EmailAddress,EmailTextID from AutoSendEmail 
open EmailCursor 
fetch next from EmailCursor into @recordID,@userID,@email,@typeID 
While ( @@Fetch_Status=0 ) 
begin 
if( @typeID=1) 
begin 
exec @result=SendRegisteredEmail @userID,@email 
if(@result=0) 
begin 
delete from [Services].[AutoSendEmail] where RecordID=@recordID 
end 
end 
fetch next from EmailCursor into @recordID,@userID,@email,@typeID 
end 
close EmailCursor 
Deallocate EmailCursor 

 

这里用游标遍历AutoSendEmail表，job设定为每分钟遍历一次，如果发送成功，就把该行记录给删除。否则保留在表中，下次遍历时，再次发送。
这样一个简单的应用型发送邮件功能就实现好了！