SQL 2008 R2 发邮件储存过程（含附件）测试

SQL 2008 R2 发邮件储存过程测试，

如执行不了 请启动 

/* 如出现错误请执行下列代码授权 sp_configure 'show advanced options',1 reconfigure go sp_configure 'xp_cmdshell',1 reconfigure go    sp_configure 'Ole Automation Procedures',1 reconfigure go */

参数有填写发件人，收件人（可用逗号填写多个收件人），标题，正文件内容，格式是HTMLBody 表格型，TextBody 文本型

附件发送请留言 数据库是否有访问附件的权限，一般附件放在服务器上测试的话是可以正常访问的，如果是HTMLBody 注意格式填写，如下代码

create PROCEDURE [dbo].[send_mailYSunKpi] @From varchar(1000) , --发件人 @To varchar(1000) , --收件人 @Subject nvarchar(128)='', --标题 @Body NVARCHAR(MAX) ='', --正文 @TypeHTML nvarchar(10)='', --发送格式 If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'. @Attachment varchar(250)='' --附件发送 --附件路径必须是数据库可以访问的路径，最好是在服务器上的路径 --with encryption /********************************************************************* /* 如出现错误请执行下列代码授权 sp_configure 'show advanced options',1 reconfigure go sp_configure 'xp_cmdshell',1 reconfigure go sp_configure 'Ole Automation Procedures',1 reconfigure go */ This stored procedure takes the parameters and sends an e-mail. All the mail configurations are hard-coded in the stored procedure. Comments are added to the stored procedure where necessary. References to the CDOSYS objects are at the following MSDN Web site: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp ***********************************************************************/ AS Declare @iMsg int Declare @hr int Declare @source varchar(255) Declare @description varchar(500) Declare @output varchar(1000) Declare @smtpserver varchar(200) Declare @sendusername varchar(200) Declare @sendpassword varchar(200) --please set the values before excute the stored procedure set @smtpserver = 'ysungj.com' --smtp服务器 set @sendusername = '*****@ysungj.com'--发送认证:用户名 set @sendpassword = '*****'--发送认证:密码 if @sendusername='' or @sendpassword='' begin raiserror 50000 'please set the @sendusername and @sendpassword values before excute the stored procedure' return -1 end --replace the quotation marks set @Subject=replace(@Subject,'''','''''') set @Body=replace(@Body,'''','''''') --************* Create the CDO.Message Object ************************ EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT --***************Configuring the Message Object ****************** -- This is to configure a remote SMTP server. -- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2' -- This is to configure the Server Name or IP address. -- Replace MailServerName by the name or IP of your SMTP Server. EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @smtpserver -- EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").Value','1' EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").Value', @sendusername EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").Value', @sendpassword -- Save the configurations to the message object. EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null -- Set the e-mail parameters. EXEC @hr = sp_OASetProperty @iMsg, 'To', @To EXEC @hr = sp_OASetProperty @iMsg, 'From', @From EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject -- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'. EXEC @hr = sp_OASetProperty @iMsg, @TypeHTML,@Body --@Body+'' if @Attachment <>'' BEGIN EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment',NULL, @Attachment; END EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL print @hr; if @@error<>0 or @hr<>0 begin raiserror 55000 '<send_mail> Error: send mail failed.' end else begin print 'Success: send mail ok.' end EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT IF @hr = 0 BEGIN SELECT @output = '<send_mail> Error Source: ' + @source PRINT @output SELECT @output = '<send_mail> Error Description: ' + @description PRINT @output END ELSE BEGIN PRINT ' sp_OAGetErrorInfo failed.' RETURN END -- Do some error handling after each step if you have to. -- Clean up the objects created. EXEC @hr = sp_OADestroy @iMsg GO

SQL 储存过程调用 过程代码如下， 

Declare @description NVARCHAR(MAX),@Attachment nvarchar(120)=''; SET @description = N'<style>table{table-layout:fixed;width:1200px;border:1px solid #000000;border-collapse:collapse;font-size:12px;empty-cells:show;}' + N'th,td{border:1px solid #000000;padding:3px;}</style>' + N'<H2>客户列表导出时间' + CONVERT(VARCHAR(19), GETDATE(), 120) + '客户情况表</H2>' --标题 + N'<table>' + N'<thead><tr>' + N'<th style="width:80px;">ID</th>' + N'<th style="width:90px;">姓名</th>' + N'<th style="width:350px;">公司</th>' + N'<th style="width:150px;">部门</th>' + N'<th style="width:80px;">职位</th>' + N'<th style="width:80px;">电话</th>' + N'<th style="width:80px;">日期</th>' + N'</tr></thead><tbody>' --表头 + CAST(( SELECT [mKey] AS td , '' , [Name] AS td , '' , [Company] AS td , '' , ISNULL([Dept], ' ') td , '' , [Zwei] AS td , '' , [Tel-Long] as td , '' , GETDATE() as td , '' FROM [YS_IMS].[dbo].[YSTel] FOR XML PATH('tr') , TYPE ) AS NVARCHAR(MAX)) + N'</tbody></table>'; SET @Attachment=N'D:\资料.xls' exec [send_mailYSunKpi] '**ix@ysungj.com','****@ysungj.com,****@qq.com','测试邮件',@description,'HTMLBody',@Attachment; --'HTMLBody' --TextBody

 效果如下图所示：