sqlserver2008自动发送邮件

1、这两天都在搞这个东西， 从开始的一点不懂， 到现在自己可以独立的完成这个功能！ 在 这个过程中， CSDN 的好多牛人都给了我很大的帮助， 在此表示十二分的感谢！ 写这篇文章， 一是为了巩固一下， 二嘛我也很希望我写的这点小东西能帮助遇到同样问题的朋友们！ 当然 这里有一部分是从网上的摘录的实现一个类似于注册平台的功能： 比如注册了一个用户， 就会向注册邮箱里发送一封邮 件。首先是要搭建一个自动发送邮件的平台，这个用sql server 2008（ sql server 2005 也有）的 database mail 就能很方便的实现。 这个完成了以后就能够发送简单的邮件了， 但是这样在 应用

2、中往往是不够，我们还要需要建一个 job 来实现自动发送的功能。database mail 的构建有两种方法，一是用 configure database mail，另外一种是用 sql 语 句完成。这两种方法中的服务器名字，我刚开始以为是随便起一个就可以的， （以为 sql server 的 database mail 会自动把这个服务器名申请为一个可用的 smtp 服务器）后来貌似不是的。 这里好像必须要是一个存在并且可用的 smtp 服务器名如果想要删除建立好的 profile 和 account 可以用如下语句代码如下 :DECLAREaccount_name sysname,prof

3、ile_name sysname;SELECTaccount_name = Nwant_to_delete_Accountprofile_name = Nwant_to_delete_Profile;- 从邮件配置文件中删除数据库邮件帐号EXEC msdb.dbo.sysmail_delete_profileaccount_spprofile_name = profile_name,account_name = account_name;- 删除数据库邮件帐号EXEC msdb.dbo.sysmail_delete_account_spaccount_name = account_name;-

4、 删除数据库邮件配置文件EXEC msdb.dbo.sysmail_delete_profile_spprofile_name = profile_name;完成了 database mail的搭建。下面我们就要开始完善自动发送邮件的功能。 我们要实现发送邮件的格式是：尊敬的： titleName感谢您的注册，你的用户名是，密码是 object你的满意是我们最大的动力。 Conclusion某某公司 Inscribe首先我们可以建两个表：一个是 AutoSendEmail 记录用户 ID 和 Email 的信息；一个是 EmailText ，一个 static table ，里面存储的是我们要

5、发送的邮件的格式。AutoSendEmail 的主要字段有 UserID ， EmailAddress ， EmailTextID （ EmailText 的一个 外键）。这里的 UserID 对应的是一个 User 表，里面存有用户的登录名及登录密码。EmailText 的主要字段有 EmailTextID ，Subject （主题）， titleName （称呼）， object（内 容)，Conclusion (结束语)， Inscribe(落款)。可能看到这里，有人会认为 EmailText 表没必 要设计的这么麻烦。有 EmailTextID ， Subject 和一个 body 就

6、够了，确实，但是这样的话， 内容中的排版格式比较麻烦。 这样设计一是为了格式方便， 一个是为了和发送其他邮件格式 统一。插入一行数据到 EmailText 表中insert into EmailText (EmailTextID,Subject,TitleName,Object,Conclusion,Inscribe) values(1, 注册成功 ,尊敬的 <UserName>:,感 谢 您 申 请 注 册 某 某 公 司 会 员 ! 你 的 注 册 帐 号 为 <LoginName> ， 密 码 为 <Password> ，请妥善保管您的密码！ ,感谢您

7、对我们公司的支持！如果任何意见或建议，欢迎来电咨询 :123-456789 。请不要 回复此邮件！ , 某某公司 )这样，用 job 每次遍历 AutoSendEmail ，从中得到 UserID 和 EmailTextID ，然后根据 UserID 可以得到相应的登录名和登录密码。同时根据 EmailTextID ，取出里面的 titleName (称呼)， object(内容)， Conclusion (结束语) ， Inscribe (落款)四个字段，用得到的登录名和登录 密码去替换 <UserName> ,<LoginName>,<Password>

8、 这样就可以拼凑成一封 邮件的完整 body 。为此，先建立一个 procedure代码如下 :CREATE PROCEDURE Services.SendRegisteredEmail(UserID int,EmailAddress nvarchar(100)- mailitem_id int OUTPUT)ASdeclare userName nvarchar(30)declare Login nvarchar(50)declare password nvarchar(50)declare regUserID intdeclare titleName nvarchar(100)declar

9、e 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 datetimedeclare body1 nvarchar(max)beginselect userName=LastName from Users.User where

10、 UserID=UserIDselect Subject1=Subject,titleName=TitleName ,object=Object,Conclusion=Conclusion,Inscri ble=Inscribe from Services.EmailText where EmailTypeID=1select titleResult=replace(titleName,<UserName>,userName)select Login=Login ,password=Password from Users.RegisteredUser where UserID=Us

11、erIDselect objectResult=replace(object,<LoginName>,Login)select objectResult=replace(objectResult,<LoginName>,Login)set body1=titleResult+N<br>+objectResult+N<br>+Conclusion+N<br> +InscribleEXEC result=msdb.dbo.sp_send_dbmail profile_name=SurmountGroupProfile, recipient

12、s=EmailAddress,subject= Subject1,body = body1, body_format = HTML;return resultend上面代码用了一个 sql 里的替换函数 replace，该函数的用法是 用第三个表达式替换第一个字符串表达式中出现的所有第二个给定字符串表达式。 语法 REPLACE ( string_expression1 , string_expression2 , string_expression3 ) 参数 string_expression1 待搜索的字符串表达式。 string_expression1 可以是字符数据或 二进制数据。s

13、tring_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 代表

14、发送成功， 1 为发送失败邮件的格式是用 HTML 形式显示有了这个存储过程，那么只要用个建个 job 就可以了。 job 主要是为了实现某些需要数 据库自动处理的功能。job 里的 command 输入如下代码：代码如下 :declare recordID intdeclare userID intdeclare email nvarchar(100)declare typeID intdeclare result intdeclare EmailCursor cursor for select RecordID,UserID,EmailAddress,EmailTextID from AutoSendEmailopen EmailCursorfetch next from EmailCursor into recordID,userID,email,typeIDWhile ( Fetch_Status=0 )beginif( typeID=1)beginexec result=SendRegisteredEmail userID,email if(result=0)begindelete from Services.AutoSendEmail where RecordI