|
?
sqlserver 发送邮件脚本
?
USE [MaganerDB]
GO
/****** 对象:? StoredProcedure [dbo].[spSendMail]??? 脚本日期: 07/16/2014 18:27:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[spSendMail]
??? @from varchar(100),??????????????? --send by
??? @to varchar(100),??????????????? --send to
??? @bcc varchar(500),??????????????? --bcc(blind carbon copy)/cc(carbon copy)
??? @subject varchar(400) = ' ',??????????????? --mail subject
??? @htmlBody varchar(8000) = ' ',??????????? --mail body content
??? @addAttachment varchar(100)??????????? --attachment,such as 'd:/fileName.xls',if there is no attachment,just input ''
as
??? declare @object int
??? declare @hr int
??? declare @source varchar(255)
??? declare @description varchar(500)
??? declare @output varchar(1000)
?????
??? declare @smtpServer varchar(50)
??? declare @smtpUsername varchar(50)
??? declare @smtpPassword varchar(50)
??? set @smtpServer = 'smtp.163.com'
??? set @smtpUsername = 'Username'
??? set @smtpPassword = 'UserPass'
?
??? --@see http://msdn.microsoft.com/en-us/library/ms526227%28v=exchg.10%29.aspxd
??? --http://schemas.microsoft.com/cdo/configuration
??? exec @hr = sp_OACreate 'CDO.Message',@object out
??? set @htmlBody = '<body><h3><font col=Red>' + @htmlBody + '</font></h3></body>'
??? --change line
??? --set @htmlBody = replace(@htmlBody,char(10),'<br/>')
??? --exec @hr = sp_OASetProperty @object,'HTMLBodyPart.Charset','GBK'
?
??? exec @hr = sp_OASetProperty @object,'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
??? exec @hr = sp_OASetProperty @object,'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value',@smtpServer
??? exec @hr = sp_OASetProperty @object,'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport").Value','25'
??? exec @hr = sp_OASetProperty @object,'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").Value','1'
??? exec @hr = sp_OASetProperty @object,'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").Value',@smtpUsername
??? exec @hr = sp_OASetProperty @object,'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").Value',@smtpPassword
?
??? exec @hr = sp_OAMethod @object,'Configuration.Fields.Update',null
??? exec @hr = sp_OASetProperty @object,'To',@to
??? exec @hr = sp_OASetProperty @object,'Bcc',@bcc
??? exec @hr = sp_OASetProperty @object,'From',@from
??? exec @hr = sp_OASetProperty @object,'Subject',@subject
??? exec @hr = sp_OASetProperty @object,'HtmlBody',@htmlBody
??? --exec @hr = sp_OASetProperty @object,'TextBody',@htmlBody(String content)
?????
??? if @addAttachment <> '' begin
???? declare @par int ;
???? exec @hr = sp_OAMethod @object,'AddAttachment',@par output,@addAttachment
??? print?? @par ;??
??? end? ;
??? if @hr <> 0
????? --? select @hr
??????? begin
??????????? exec @hr = sp_OAGetErrorInfo null,@source out,@description out
??????????? if @hr = 0
??????????????? begin
??????????????????? select @output = ' Source: '+@source
??????????????????? print @output
??????????????????? select @output = ' Description: '+@description
??????????????????? print @output
??????????????? end
??????????? else
??????????????? begin
??????????????????? print ' sp_OAGetErrorInfo failure!'
??????????????????? return
??????????????? end
??????? end
?? exec @hr = sp_OAMethod @object,'Send',null
?
??? --check error
??? if @hr <>0
??????? select @hr
??????? begin
??????????? exec @hr = sp_OAGetErrorInfo null,@description out
??????????? if @hr = 0
??????????????? begin
??????????????????? select @output = ' Source: '+@source
??????????????????? print @output
??????????????????? select @output = ' Description: '+@description ??????????????????? print @output??????????????? end??????????? else??????????????? begin??????????????????? print ' sp_OAGetErrorInfo failure!'??????????????????? return??????????????? end??????? end??? print 'Send Mail Success!'? exec @hr = sp_OADestroy @object
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
