SQL邮件自动应答
类别: MSSQL教程 
SQLMail可以收发邮件，可能好多人都有xp_sendmail发送过邮件，这里介绍一种邮件服务器接收邮件后，根据邮件内容(只能是查询语句) 把邮件中的查询语句执行后的结果以附件形式自动返回给发送邮件者。
eg:
发送邮件，邮件内容为: select top 10 id,name from sysobjects则服务器自动回复邮件，内容为上述查询语句的执行结果，以附件形式发送。
1:当然是先配置好SQL邮件服务了，这方面的资料比较多，此处不赘述。
2: 把sp_processmail稍作修改 (把发件人的邮件地址解析为真正的邮箱地址)
Alter procedure sp_processmail
@subject varchar(255)=NULL,
@filetype varchar(3)=’txt’,
@separator varchar(3)=’tab’,
@set_user varchar(132)=’guest’,
@dbuse varchar(132)=’master’
as
declare @status int
declare @msg_id varchar(64)
declare @originator varchar(255)
declare @originator_address varchar(255)
declare @a varchar(255)
declare @cc_list varchar(255)
declare @msgsubject varchar(255)
declare @query varchar(8000)
declare @messages int
declare @mapifailure int
declare @resultmsg varchar(80)
declare @filename varchar(12)
declare @current_msg varchar(64)
select @messages=0
select @mapifailure=0
if @separator=’tab’ select @separator=CHAR(9)
/* get first message id */
exec @status = master.dbo.xp_findnextmsg
@msg_id=@msg_id output,
@unread_only=’true’
if @status <> 0
select @mapifailure=1
while (@mapifailure=0)
begin
if @msg_id is null break
if @msg_id = ’’ break
exec @status = master.dbo.xp_readmail
@msg_id=@msg_id,
@originator=@originator output,
@cc_list=@cc_list output,
@subject=@msgsubject output,
@message=@query output,
@peek=’true’,
@originator_address= @originator_address output,
@suppress_attach=’true’
if @status <> 0
begin
select @mapifailure=1
break
end
/* get new message id before processing & deleting current */
select @current_msg=@msg_id
exec @status = master.dbo.xp_findnextmsg
@msg_id=@msg_id output,
@unread_only=’true’
if @status <> 0
begin
select @mapifailure=1
end
if ((@subject IS NULL) OR (@subject=@msgsubject))
begin
/* generate random filename */
select @filename=’SQL’ + convert(varchar,ROUND(RAND()*100000,0)) + ’.’ + @filetype
exec @status = master.dbo.xp_sendmail
-- @recipients=@originator,
@recipients=@originator_address,
@copy_recipients=@cc_list,
@message=@query,
@query=@query,
@subject=’Query Results’,
@separator=@separator,
@width=256,
@attachments=@filename,
@attach_results=’true’,
@no_output=’false’,
@echo_error=’true’,
@set_user=@set_user,
@dbuse=@dbuse
if @status <> 0
begin
select @mapifailure=1
break
end
select @messages=@messages+1
exec master.dbo.xp_deletemail @current_msg
end /* end of xp_sendmail block */
end /* end of xp_findnextmsg loop */
/* finished examining the contents of inbox; now send results */
if @mapifailure=1
begin
raiserror(15079,-1,-1,@messages)
return(1)
end
else
return(0)
-- sp_processmail
3:在master数据库下建一个存储过程
use master
create proc answerMail
as
sp_processmail @subject = ’sql’,@filetype = ’CSV’,@separator =’,’,@set_user = ’dbo’ ,@dbuse = ’数据库名’
4: 用job调度此存储过程。
5: 现在可以测试了。发一封邮件给服务器上配置的邮箱地址。
主题为 sql
内容为 一句简单的查询语句 eg: select getdate()
发送
6: 等到job调度时间到后即可收到回复。
eg:
发送邮件，邮件内容为: select top 10 id,name from sysobjects则服务器自动回复邮件，内容为上述查询语句的执行结果，以附件形式发送。
1:当然是先配置好SQL邮件服务了，这方面的资料比较多，此处不赘述。
2: 把sp_processmail稍作修改 (把发件人的邮件地址解析为真正的邮箱地址)
Alter procedure sp_processmail
@subject varchar(255)=NULL,
@filetype varchar(3)=’txt’,
@separator varchar(3)=’tab’,
@set_user varchar(132)=’guest’,
@dbuse varchar(132)=’master’
as
declare @status int
declare @msg_id varchar(64)
declare @originator varchar(255)
declare @originator_address varchar(255)
declare @a varchar(255)
declare @cc_list varchar(255)
declare @msgsubject varchar(255)
declare @query varchar(8000)
declare @messages int
declare @mapifailure int
declare @resultmsg varchar(80)
declare @filename varchar(12)
declare @current_msg varchar(64)
select @messages=0
select @mapifailure=0
if @separator=’tab’ select @separator=CHAR(9)
/* get first message id */
exec @status = master.dbo.xp_findnextmsg
@msg_id=@msg_id output,
@unread_only=’true’
if @status <> 0
select @mapifailure=1
while (@mapifailure=0)
begin
if @msg_id is null break
if @msg_id = ’’ break
exec @status = master.dbo.xp_readmail
@msg_id=@msg_id,
@originator=@originator output,
@cc_list=@cc_list output,
@subject=@msgsubject output,
@message=@query output,
@peek=’true’,
@originator_address= @originator_address output,
@suppress_attach=’true’
if @status <> 0
begin
select @mapifailure=1
break
end
/* get new message id before processing & deleting current */
select @current_msg=@msg_id
exec @status = master.dbo.xp_findnextmsg
@msg_id=@msg_id output,
@unread_only=’true’
if @status <> 0
begin
select @mapifailure=1
end
if ((@subject IS NULL) OR (@subject=@msgsubject))
begin
/* generate random filename */
select @filename=’SQL’ + convert(varchar,ROUND(RAND()*100000,0)) + ’.’ + @filetype
exec @status = master.dbo.xp_sendmail
-- @recipients=@originator,
@recipients=@originator_address,
@copy_recipients=@cc_list,
@message=@query,
@query=@query,
@subject=’Query Results’,
@separator=@separator,
@width=256,
@attachments=@filename,
@attach_results=’true’,
@no_output=’false’,
@echo_error=’true’,
@set_user=@set_user,
@dbuse=@dbuse
if @status <> 0
begin
select @mapifailure=1
break
end
select @messages=@messages+1
exec master.dbo.xp_deletemail @current_msg
end /* end of xp_sendmail block */
end /* end of xp_findnextmsg loop */
/* finished examining the contents of inbox; now send results */
if @mapifailure=1
begin
raiserror(15079,-1,-1,@messages)
return(1)
end
else
return(0)
-- sp_processmail
3:在master数据库下建一个存储过程
use master
create proc answerMail
as
sp_processmail @subject = ’sql’,@filetype = ’CSV’,@separator =’,’,@set_user = ’dbo’ ,@dbuse = ’数据库名’
4: 用job调度此存储过程。
5: 现在可以测试了。发一封邮件给服务器上配置的邮箱地址。
主题为 sql
内容为 一句简单的查询语句 eg: select getdate()
发送
6: 等到job调度时间到后即可收到回复。
