基于SQL调用Com组件来发送邮件
这个需求是公司有个文控中心,如果有用增删改了文件信息希望可以发邮件通知到有权限的人。当然方式很多。
这里是用数据库作业来完成
JOB+Com,这里用的com组件是Jmail
当然你需要把com组件放到system32下面
下面是实现方式
CREATE PROC dbo.dcc_Send_Email AS BEGIN SET NOCOUNT ON DECLARE @lasttime DATETIME --获取最后一次发送的时间 IF Not EXISTS(SELECT lastsendtime FROM dcc_lastsend) begin set @lasttime=GETDATE() END ELSE BEGIN SELECT @lasttime=lastsendtime FROM dcc_lastsend END --SELECT @lasttime --SELECT * FROM dcc_changereaon WHERE createdate>=@lasttime DECLARE @sendto VARCHAR(500)--发送邮件给谁 DECLARE @aliasname VARCHAR(500)--档案名称 DECLARE @filename VARCHAR(500)--名称 DECLARE @userid INT --用户ID DECLARE @dccid INT --文件夹ID DECLARE @dccfileid INT --文件ID DECLARE @action VARCHAR(500) --操作类型 1 新增 2 修改 3删除 DECLARE @changeid int DECLARE @reasons VARCHAR(8000) --原因 DECLARE @html VARCHAR(8000) ---html格式内容 DECLARE @table VARCHAR(8000) ---html格式内容 DECLARE @senduiserid VARCHAR(8000) --通知的用户 DECLARE @sendfileid VARCHAR(8000) --更改的文件用户 DECLARE @temp TABLE (id INT ,ACTION varchar(50), aliasname varchar(50),FILENAME varchar(50)) DECLARE @rcount INT SET @html=''-- SET @table='' DECLARE @index INT--奇数还是偶数行 SET @index=0 DECLARE CusCursor CURSOR FOR --外层游标查询用户 SELECT id,DccMail FROM view_dcc_users WHERE DccMail<>'' AND IsLock=0 OPEN CusCursor FETCH NEXT FROM CusCursor INTO @userid,@sendto WHILE (@@FETCH_STATUS = 0) BEGIN SET @table='' SET @index=0 SET @html = '' SET @rcount=0 DELETE @temp INSERT INTO @temp (id,action,aliasname,filename) SELECT a.id,[action],a.aliasname,a.filename FROM dcc_changereaon a WHERE a.createdate>=@lasttime AND dccid IN (SELECT folderid FROM dbo.dcc_rights WHERE userid=@userid AND enabled=1) SET @rcount = @@ROWCOUNT IF @rcount>0 BEGIN DECLARE cur CURSOR FOR SELECT * FROM @temp --内层游标查询用户有那些权限 OPEN cur SET @html='<html><head><style type="text/css">table{font-family:"Trebuchet MS", Arial, Helvetica, sans-serif;width:100%;border-collapse:collapse;}td,th{font-size:1em;border:1px solid #98bf21;padding:3px 7px 2px 7px;}th {font-size:1.1em;text-align:left;padding-top:5px;padding-bottom:4px;background-color:#A7C942;color:#ffffff;}tr.alt td {color:#000000;background-color:#EAF2D3;}</style></head><body>各位:<br>兹通知下述文件有变动,贵部可随时登录文控网页(www.gardenchinagroup.com:82) 查阅:<br><br><br><table><tr><th>档案名称</th><th>名称</th><th>备注(标注新增/更新/取消)</th></tr>' FETCH NEXT FROM cur INTO @changeid,@action,@aliasname,@filename WHILE (@@FETCH_STATUS = 0) BEGIN INSERT INTO dbo.dcc_logs ( changeid, userid, createtime ) VALUES ( @changeid, -- changeid - int @userid, -- userid - int GETDATE() -- createtime - smalldatetime ) IF @index %2<>0 SET @table=@table+'<tr class=''alt''><td>'+@filename+'</td><td>'+@aliasname+'</td><td>'+@action+'</td></tr>' ELSE SET @table=@table+'<tr><td>'+@filename+'</td><td>'+@aliasname+'</td><td>'+@action+'</td></tr>' SET @index=@index+1 fetch next from cur into @changeid,@action,@aliasname,@filename END CLOSE cur DEALLOCATE cur SET @html=@html+@table+'</table></body></html>' --PRINT @sendto EXEC myCommData.dbo.my_SendMail '文件新增/更新/取消通知',@html,'DCC',@sendto,'','fengmin.dg@gardenchinagroup.com,foxbuilder.dg@gardenchinagroup.com','joey.dg@gardenchinagroup.com',1 END --SELECT @html FETCH NEXT FROM CusCursor INTO @userid,@sendto END CLOSE CusCursor DEALLOCATE CusCursor IF Not EXISTS(SELECT lastsendtime FROM dcc_lastsend) --更新最后一次发送时间 begin INSERT INTO dbo.dcc_lastsend( lastsendtime )VALUES(GETDATE()) END ELSE BEGIN UPDATE dcc_lastsend SET lastsendtime=GETDATE() END END GO
CREATE PROC dbo.my_SendMail @Subject varchar(100), -- 主题 @Body varchar(8000), -- 内容 @Sender varchar(100), -- 发件人姓名 @ToList varchar(2000), -- 发送邮件地址, 可以多个地址,用","隔开 @ccList varchar(2000)='', -- 抄送地址 @BccList varchar(2000)='', -- 秘抄送地址 @ReturnEmail VARCHAR(500)='', -- 回执到指定邮箱 @ReturnReceipt bit = 0 --是否回执 AS /* EXEC dbo.my_SendMail @Subject = 'today is a luck day', -- varchar(100) @Body = 'today is a luck day 20130', -- varchar(8000) @Sender = 'system', -- varchar(100) @ToList = 'fengmin.dg@gardenchinagroup.com', -- varchar(2000) @ccList = 'fengmin.dg@gardenchinagroup.com', -- varchar(2000) @BccList='foxbuilder.dg@gardenchinagroup.com', @ReturnEmail='foxbuilder.dg@gardenchinagroup.com', @ReturnReceipt=1 */ Declare @object int Declare @hr int EXEC @hr = sp_OACreate 'jmail.Message', @object OUT EXEC @hr = sp_OASetProperty @object, 'Silent' , 1 EXEC @hr = sp_OASetProperty @object, 'Logging' , 1 EXEC @hr = sp_OASetProperty @object, 'Charset' , 'GB2312' EXEC @hr = sp_OASetProperty @object, 'From' , @Sender EXEC @hr = sp_OASetProperty @object, 'ISOEncodeHeaders' ,0 EXEC @hr = sp_OASetProperty @object, 'From' , 'mis.dg@gardenchinagroup.com' EXEC @hr = sp_OASetProperty @object, 'MailServerUserName' , 'mis.dg' EXEC @hr = sp_OASetProperty @object, 'MailServerPassWord' , 'Edp2469851' EXEC @hr = sp_OASetProperty @object, 'Subject' , @Subject EXEC @hr = sp_OASetProperty @object, 'HTMLBody' , @Body IF @ReturnReceipt=1 BEGIN EXEC @hr = sp_OASetProperty @object, 'ReturnReceipt' ,1 --是否需要回执 DECLARE @backemail VARCHAR(2000) SET @backemail='AddNativeHeader (Disposition-Notification-To,'+@ReturnEmail+')' EXEC @hr = sp_OAMethod @object, @backemail,NULL END declare @i int set @i=1 declare @mail varchar(100), @addmail varchar(100),@bccmail varchar(100) -- 添加发件人 set @mail = '' set @mail =dbo.my_SplitStr(@ToList, ',', @i) while not @mail is null begin if @mail<>'' begin set @addmail= 'AddRecipient ("' + @mail + '")' EXEC @hr = sp_OAMethod @object, @addmail, null end set @i=@i+1 set @mail =dbo.my_SplitStr(@ToList, ',', @i) end -- 添加抄送 set @mail = '' set @i =1 set @mail =dbo.my_SplitStr(@ccList, ',', @i) while not @mail is null begin if @mail<>'' begin set @addmail= 'AddRecipientCC ("' + @mail + '")' EXEC @hr = sp_OAMethod @object, @addmail, null end set @i=@i+1 set @mail =dbo.my_SplitStr(@ccList, ',', @i) end --秘密抄送 set @mail = '' set @i =1 set @mail =dbo.my_SplitStr(@BccList, ',', @i) while not @mail is null begin if @mail<>'' begin set @bccmail= 'AddRecipientBCC ("' + @mail + '")' EXEC @hr = sp_OAMethod @object, @bccmail, null end set @i=@i+1 set @mail =dbo.my_SplitStr(@BccList, ',', @i) end -- 开始发送 EXEC @hr = sp_OAMethod @object, 'Send("192.168.0.3")', null --判断出错 IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @object END PRINT 'success' EXEC @hr = sp_OADestroy @object GO