菜单 学习猿地 - LMONKEY

VIP

开通学习猿地VIP

尊享10项VIP特权 持续新增

知识通关挑战

打卡带练!告别无效练习

接私单赚外块

VIP优先接,累计金额超百万

学习猿地私房课免费学

大厂实战课仅对VIP开放

你的一对一导师

每月可免费咨询大牛30次

领取更多软件工程师实用特权

入驻
201
0

sqlserver2008自动发送邮件

原创
05/13 14:22
阅读数 46410

复制代码 代码如下:

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&编程客栈gt;,<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) 
decla编程客栈re @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,@Conclushttp://www.cppcns.comion=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+Nwww.cppcns.com'<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 http://www.cppcns.com@recordID,@userID,@email,@typeID 
end 
close EmailCursor 
Deallocate EmailCursor 

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

本文标题: sqlserver2008自动发送邮件
本文地址: http://www.cppcns.com/shujuku/mssql/104274.html

发表评论

0/200
201 点赞
0 评论
收藏
为你推荐 换一批