单独运行过程，能正常运行，邮件及附件能正常发送
但现在将该过程添加到作业里，邮件能正常发送，但没有附件，不知道为啥
SqlServer版本:2000
if exists(select 1 from sysobjects where name = 'spSendMail' and type = 'P')
drop procedure spSendMail
go
create procedure 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 = 'mail.qq.com'
set @smtpUsername = ''
set @smtpPassword = ''
--@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 <> ''
exec @hr = sp_OAMethod @object,'AddAttachment',null,@addAttachment
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,@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
print 'Send Mail Success!'
exec @hr = sp_OADestroy @object
作业分两步：
1：过程从数据库获取数据生成excel：D:\Install\Task\Rpt\Rpt.xls
2：过程发送邮件，附件为该excel
exec spSendMail '','','','subject','body','D:/Install/Task/Rpt/Rpt.xls'
