SQL server 邮件 无法使用 sql自动发送邮件

实现一个类似于注册平台的功能：比如注册了一个用户，就会向注册邮箱里发送一封邮件。首先是要搭建一个自动发送邮件的平台，这个用sql server 2008（sql server 2005也有）的database mail就能很方便的实现。这个完成了以后就能够发送简单的邮件了，但是这样在应用中往往是不够，我们还要需要建一个job来实现自动发送的功能。

database mail的构建有两种方法，一是用configure database mail，这个只要手动的配置一下就可以了，具体的可以参考

另外一种是用sql语句完成。网上有很多中方法，下面的链接是我发的一个帖子，爱新觉罗.毓华，siegebaoniu，还有加勒比小飞猪都给出了很详细的描述。我用的是加勒比小飞猪的方法

这两种方法中的服务器名字，我刚开始以为是随便起一个就可以的，（以为sql server的database mail会自动把这个服务器名申请为一个可用的smtp服务器）后来貌似不是的。这里好像必须要是一个存在并且可用的smtp服务器名

如果想要删除建立好的profile和account可以用如下语句

[c-sharp]  view plain copy

1. DECLARE  
2.     @account_name sysname,  
3.     @profile_name sysname;  
4. SELECT  
5. 'want_to_delete_Account'  
6. 'want_to_delete_Profile';  
7.    
8. -- 从邮件配置文件中删除数据库邮件帐号  
9. EXEC msdb.dbo.sysmail_delete_profileaccount_sp  
10.     @profile_name = @profile_name,  
11.   @account_name = @account_name;  
12.      
13. -- 删除数据库邮件帐号  
14. EXEC msdb.dbo.sysmail_delete_account_sp  
15.     @account_name = @account_name;  
16.    
17. -- 删除数据库邮件配置文件  
18. EXEC msdb.dbo.sysmail_delete_profile_sp  
19.     @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 

[c-sharp]  view plain copy

1. CREATE PROCEDURE [Services].[SendRegisteredEmail]  
2. (   
3. int,  
4.     @EmailAddress nvarchar(100)  
5. --  @mailitem_id int OUTPUT  
6.  )  
7. AS  
8. declare @userName nvarchar(30)  
9. declare @Login nvarchar(50)  
10. declare @password nvarchar(50)  
11. declare @regUserID int  
12. declare @titleName nvarchar(100)  
13. declare @titleResult nvarchar(100)  
14. declare @object nvarchar(600)  
15. declare @objectResult nvarchar(600)  
16. declare @Subject1 nvarchar(100)  
17. declare @Conclusion nvarchar(600)  
18. declare @Inscrible nvarchar(100)  
19. declare @result int  
20. --declare @time datetime  
21. declare @body1 nvarchar(max)  
22. begin  
23. select @userName=LastName from [Users].[User] where UserID=@UserID  
24. select @Subject1=Subject,@titleName=TitleName ,@object=Object,@Conclusion=Conclusion,@Inscrible=Inscribe from [Services].[EmailText] where EmailTypeID=1  
25. select @titleResult=replace(@titleName,'<UserName>',@userName)  
26. select @Login=Login ,@password=Password from [Users].[RegisteredUser] where UserID=@UserID  
27. select @objectResult=replace(@object,'<LoginName>',@Login)  
28. select @objectResult=replace(@objectResult,'<LoginName>',@Login)  
29. set @body1=@titleResult+N'<br>'+@objectResult+N'<br>'+@Conclusion+N'<br>'+@Inscrible  
30. EXEC @result=msdb.dbo.sp_send_dbmail @profile_name='SurmountGroupProfile',  
31.                              @recipients=@EmailAddress,  
32.                              @subject= @Subject1,  
33.                              @body = @body1,  
34. 'HTML';  
35. return @result  
36.                               
37. end  
38.    
39.

 

用第三个表达式替换第一个字符串表达式中出现的所有第二个给定字符串表达式。

语法 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输入如下代码：

 

[c-sharp]  view plain copy

1. declare @recordID int  
2. declare @userID int  
3. declare @email nvarchar(100)  
4. declare @typeID int  
5. declare @result int  
6. declare EmailCursor cursor for select RecordID,UserID,EmailAddress,EmailTextID from AutoSendEmail  
7. open EmailCursor  
8. fetch next from EmailCursor into @recordID,@userID,@email,@typeID  
9. While ( @@Fetch_Status=0 )  
10. begin  
11. if( @typeID=1)  
12. begin  
13. exec @result=SendRegisteredEmail @userID,@email  
14. if(@result=0)  
15. begin  
16. delete from [Services].[AutoSendEmail] where RecordID=@recordID  
17. end  
18. end  
19. fetch next from EmailCursor into @recordID,@userID,@email,@typeID  
20. end  
21. close EmailCursor  
22. Deallocate EmailCursor

这里用游标遍历AutoSendEmail表，job设定为每分钟遍历一次，如果发送成功，就把该行记录给删除。否则保留在表中，下次遍历时，再次发送。

这样一个简单的应用型发送邮件功能就实现好了！

冻死我了，呵呵，得赶紧躲到被窝里去了！如果看后有什么好的意见或是建议，欢迎留言！