使用SqlServer2005的Reporting Services,配置界面里电子邮件部分只有发件人地址和SMTP服务器可写,当前传递方法默认是使用SMTP服务器。
打开ReportServer下的rsreportserver.config文件,找到RSEmailDPConfiguration节点,修正如下:
<RSEmailDPConfiguration>
<SMTPServer>smtp.263.net</SMTPServer>
<SMTPServerPort></SMTPServerPort>
<SMTPAccountName>邮件帐号</SMTPAccountName>
<SMTPConnectionTimeout></SMTPConnectionTimeout>
<SMTPServerPickupDirectory></SMTPServerPickupDirectory>
<SMTPUseSSL></SMTPUseSSL>
<SendUsing>2</SendUsing>
<SendUser>邮件帐号@263.net</SendUser>
<SendPassword>邮件帐号密码</SendPassword>
<SMTPAuthenticate>2</SMTPAuthenticate>(此处安装微软提示:远程的SMTP服务器如果需要认证需要设置为2)
<From>邮件帐号@263.net</From>
<EmbeddedRenderFormats>
<RenderingExtension>MHTML</RenderingExtension>
</EmbeddedRenderFormats>
<PrivilegedUserRenderFormats></PrivilegedUserRenderFormats>
<ExcludedRenderFormats>
<RenderingExtension>HTMLOWC</RenderingExtension>
<RenderingExtension>NULL</RenderingExtension>
<RenderingExtension>RGDI</RenderingExtension>
</ExcludedRenderFormats>
<SendEmailToUserAlias>True</SendEmailToUserAlias>
<DefaultHostName></DefaultHostName>
<PermittedHosts></PermittedHosts>
</RSEmailDPConfiguration>以上配置不知道有什么错误,总之发送错误,RS的日志提示如下:
Error sending email. System.Runtime.InteropServices.COMException (0x80040211): 邮件无法发送到 SMTP 服务器。传输错误代码为 0x80040217。服务器响应为 not availableGoogle了一遍,没有什么结果,帮助文档和微软官网上讲到的也不多,不知道有没有高手能帮忙提点一下!
打开ReportServer下的rsreportserver.config文件,找到RSEmailDPConfiguration节点,修正如下:
<RSEmailDPConfiguration>
<SMTPServer>smtp.263.net</SMTPServer>
<SMTPServerPort></SMTPServerPort>
<SMTPAccountName>邮件帐号</SMTPAccountName>
<SMTPConnectionTimeout></SMTPConnectionTimeout>
<SMTPServerPickupDirectory></SMTPServerPickupDirectory>
<SMTPUseSSL></SMTPUseSSL>
<SendUsing>2</SendUsing>
<SendUser>邮件帐号@263.net</SendUser>
<SendPassword>邮件帐号密码</SendPassword>
<SMTPAuthenticate>2</SMTPAuthenticate>(此处安装微软提示:远程的SMTP服务器如果需要认证需要设置为2)
<From>邮件帐号@263.net</From>
<EmbeddedRenderFormats>
<RenderingExtension>MHTML</RenderingExtension>
</EmbeddedRenderFormats>
<PrivilegedUserRenderFormats></PrivilegedUserRenderFormats>
<ExcludedRenderFormats>
<RenderingExtension>HTMLOWC</RenderingExtension>
<RenderingExtension>NULL</RenderingExtension>
<RenderingExtension>RGDI</RenderingExtension>
</ExcludedRenderFormats>
<SendEmailToUserAlias>True</SendEmailToUserAlias>
<DefaultHostName></DefaultHostName>
<PermittedHosts></PermittedHosts>
</RSEmailDPConfiguration>以上配置不知道有什么错误,总之发送错误,RS的日志提示如下:
Error sending email. System.Runtime.InteropServices.COMException (0x80040211): 邮件无法发送到 SMTP 服务器。传输错误代码为 0x80040217。服务器响应为 not availableGoogle了一遍,没有什么结果,帮助文档和微软官网上讲到的也不多,不知道有没有高手能帮忙提点一下!
<SMTPServerPort> </SMTPServerPort>
<SMTPAccountName>邮件帐号 </SMTPAccountName>
检查一下这几项设置正确没有?
估计还是XML中节点名称或者少配置了什么,至于具体少了什么或节点名称哪里不对,请大家指导一下!
Configure SQL Server Reporting Services (SSRS) for non-default SMTP
http://blog.tech4him.com/2009/02/configure-sql-server-reporting-services-ssrs-for-non-default-smtp/
http://topic.csdn.net/u/20080821/14/c5f021ed-ba32-4ba9-9962-63b28aa59a27.html
楼主查下263 smtp端口号填在这里.
10楼的内容我看了一下,发邮件所需的帐号密码没有填上,所以这个恐怕不是我想要的。
11楼的内容和我的错误差不多,其中提到了SQLMail,按照这个链接提示(http://blog.csdn.net/iwilltrybest/archive/2008/05/26/2481666.aspx)SQLMail我倒是配置成了(不过发现,发送263发给QQ邮箱能收到SQLmail,发给另一个cnnb.com.cn的邮箱就不行,不过这个至少证明可以发送邮件成功),但调用SQLMail,需要用存储过程,而不是通过对Reporting Service的配置可以完成,所以不适合我目前的需求(通过配置自行发送,而无需写代码)。目前来看,还没有一个好的解决方法。再次感谢 苦*行*僧 的热心指点!希望还有更多大虾帮助一下了!
而接收邮件需要检查POP3协议和设置是否正常。
GO
/****** Object: StoredProcedure [dbo].[SendMail] Script Date: 06/01/2009 09:10:43 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GOALTER Procedure [dbo].[SendMail]
@Sender varChar(50)=null, --發件人地址
@SenderName varChar(50)=null,--發件人姓名
@strRecipients varChar(200),--收件人地址,多個人用";"分開
@strSubject varChar(200),--主旨
@strMessage varChar( 2000),--正文
@sql varChar(50)=null --如果有附件,可以在這裡寫SQL語句
AsDeclare @SplitStr varchar(1) --定义邮件地址分割符变量
Declare @strTemp varchar(200) --定义多个收件人字符串临时变量
Declare @email varchar(50) --用分割符分割后的单个收件人字符串变量Declare @SenderAddress varChar(50)
Declare @Attach varChar(200)Declare @DefaultSender varChar(50)
Declare @MailServer varChar(50)
Declare @User varChar(50)
Declare @Pass varChar(50)
--Declare @SenderName varChar(50)
Declare @AttachDir varChar(100)--初始化缺省变量
Set @DefaultSender='[email protected]'
Set @MailServer='172.20.100.21'
Set @User='jufei'
Set @Pass='jiang12475+'
--Set @SenderName='執行結果'
Set @AttachDir='E:\LOG\WebData\Jmail\'+Replace(Replace(Replace(Convert(varChar(19),GetDate(),120),'-',''),' ',''),':','')+'.txt'--将Email地址分割符统一为分号
set @SplitStr=';'
Set @strTemp=@strRecipients+@SplitStr+'end'
Set @strTemp=Replace(@strTemp,',',';')
--判斷發件人
if (@SenderName is null) or(len(@SenderName)=0)
begin
Set @SenderName='執行結果'
end
--判断是否有sql语句
If (@Sql is Null) Or (len(@Sql)=0)
Set @AttachDir=Null
Else
Begin
Declare @CmdStr varChar(200)
Set @CmdStr='bcp "'+@Sql+'" queryout '+@AttachDir+' -c'
EXEC master..xp_cmdshell @CmdStr
Endwhile CharIndex(@SplitStr,@strTemp,1)<>0
Begin
Set @email=left(@strTemp,CharIndex(@SplitStr,@strTemp,1)-1)
Set @strTemp=right(@strTemp,len(@strTemp)-len(@email)-1)
If (@Sender Is Null) Or (Len(@Sender)=0)
Set @SenderAddress=@DefaultSender
Else
Set @SenderAddress=@Sender
--Print @email EXEC sp_jmail_send @sender=@SenderAddress,@sendername=@SenderName,
@serveraddress=@MailServer,@MailServerUserName=@User,@MailServerPassword=@Pass,
@recipient=@email,@subject=@strSubject,@mailbody=@strMessage,@attachment=@AttachDir
end
USE [MES]
GO
/****** Object: StoredProcedure [dbo].[sp_jmail_send] Script Date: 06/01/2009 09:11:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO--CREATE PROCEDURE [OWNER].[PROCEDURE NAME] AS
ALTER Procedure [dbo].[sp_jmail_send]
@sender varchar(100),--發送人的郵件地址
@sendername varchar(100)='',--發送人
@serveraddress varchar(255)='172.20.100.21',--郵件服務器地址
@MailServerUserName varchar(255)=null,--服務器的用戶名
@MailServerPassword varchar(255)=null,--密碼
@recipient varchar(255),--收件人
@recipientBCC varchar(200)=null,--
@recipientBCCName varchar(200)=null,
@recipientCC varchar(200)=null,
@recipientCCName varchar(100)=null,
@attachment varchar(100) =null,
@subject varchar(255),--主旨
@mailbody text--內容
AsDeclare @object int,@hr int,@rc int,@output varchar(400),@description varchar (400),@source varchar(400)--创建JMail.Message对象Exec @hr = sp_OACreate 'jmail.message', @object OUTPUT--设置邮件编码
Exec @hr = sp_OASetProperty @object, 'Charset', 'base64'--身份验证
If Not @MailServerUserName is null
Exec @hr = sp_OASetProperty @object, 'MailServerUserName',@MailServerUserName
If Not @MailServerPassword is null
Exec @hr = sp_OASetProperty @object, 'MailServerPassword',@MailServerPassword--设置邮件基本参数
Exec @hr = sp_OASetProperty @object, 'From', @sender
Exec @hr = sp_OAMethod @object, 'AddRecipient', NULL , @recipient
Exec @hr = sp_OASetProperty @object, 'Subject', @subject
Exec @hr = sp_OASetProperty @object, 'Body', @mailbody--设置其它参数
if not @attachment is null
exec @hr = sp_OAMethod @object, 'Addattachment', NULL , @attachment,'false'
print @attachment
If (Not @recipientBCC is null) And (Not @recipientBCCName is null)
Exec @hr = sp_OAMethod @object, 'AddRecipientBCC', NULL , @recipientBCC,@recipientBCCName
Else If Not @recipientBCC is null
Exec @hr = sp_OAMethod @object, 'AddRecipientBCC', NULL , @recipientBCCIf (Not @recipientCC is null) And (Not @recipientCCName is null)
Exec @hr = sp_OAMethod @object, 'AddRecipientCC', NULL , @recipientCC,@recipientCCName
Else If Not @recipientCC is null
Exec @hr = sp_OAMethod @object, 'AddRecipientCC', NULL , @recipientCCIf Not @sendername is null
Exec @hr = sp_OASetProperty @object, 'FromName', @sendername--调用Send方法发送邮件
Exec @hr = sp_OAMethod @object, 'Send', null,@serveraddress--捕获JMail.Message异常
Exec @hr = sp_OAGetErrorInfo @object, @source OUTPUT, @description OUTPUTif (@hr = 0)
Begin
Set @output='錯誤源: '+@source
Print @output
Select @output = '錯誤描述: ' + @description
Print @output
End
Else
Begin
Print '获取错误信息失败!'
Return
End--释放JMail.Message对象
Exec @hr = sp_OADestroy @object
等了这么多天,还是没有解决,打算暂时放下不管了!如果有高人路过,请不妨留言指导一下!
谢谢!
windows放火墙也可以阻止邮件发送的.