SQL Server 定时发送带附件的邮件

Create PROCEDURE [dbo].[job_AutoMail_xxd]   
  
AS  
BEGIN  
    SET NOCOUNT ON;  
  
--获取数据  
  
truncate table Db_Name.dbo.Table_Name  
insert into Db_Name.dbo.Table_Name  
    select * from openquery(Link_Name,'exec store_procedure_name')   
  
exec master..xp_cmdshell 'del c:/*.xls'  
EXEC master..xp_cmdshell 'bcp 数据库名.表名 out c:/temp1.xls -c -q -S 服务名 -U sa -P 密码  
  
--定义邮件参数  
DECLARE @To AS varchar(500)  
SET @To = 'to@mailserver.com'  
--SET @To = 'xuexd@etpass.com'  
  
DECLARE @From AS varchar(500)  
SET @From = ' from <from@mailserver.com>'  
DECLARE @Subject AS varchar(500)  
SET @Subject = 'Subject'  
DECLARE @Body AS varchar(8000)  
SET @Body = 'Contents'  
--SET @Body = 'From xue with auto sqlmail on '+convert(char(19),getdate(),121)+''  
DECLARE @Attachment as varchar(250)  
set @Attachment = 'c:/*.xls'  
  
  
-------------------------------------------------------------------------  
-----------------------------邮件发送部分--------------------------------  
-------------------------------------------------------------------------  
  
  
Declare @object int  
Declare @hr int  
  
EXEC @hr = sp_OACreate 'CDO.Message', @object OUT  
  
EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value', '2'  
--SMTP服务器地址  
EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'smtp server'  
EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport").Value', '25'  
--SMTP认证方式，为不需要认证，为明文密码认证，为SSL  
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', 'username'  
--邮箱密码  
EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").Value', 'password'  
  
EXEC @hr = sp_OAMethod @object, 'Configuration.Fields.Update', null  
EXEC @hr = sp_OASetProperty @object, 'To', @To  
EXEC @hr = sp_OASetProperty @object, 'From', @From  
EXEC @hr = sp_OASetProperty @object, 'Subject', @Subject  
  
-- Text格式  
--EXEC @hr = sp_OASetProperty @object, 'BodyFormat', 'MailFormat.Text'  
--EXEC @hr = sp_OASetProperty @object, 'BodyPart.Charset', 'GB2312'  
--EXEC @hr = sp_OASetProperty @object, 'TextBody', @Body  
  
--HTML格式的邮件  
EXEC @hr = sp_OASetProperty @object, 'BodyFormat', 'MailFormat.Html'  
SET @Body = REPLACE(@Body,CHAR(10),'<br/>')  
SET @Body = REPLACE(@Body,' ',' ')  
EXEC @hr = sp_OASetProperty @object, 'HTMLBodyPart.Charset', 'GB2312'  
EXEC @hr = sp_OASetProperty @object, 'HtmlBody',@Body  
  
--调用发送方法发送邮件  
if @Attachment <> ' '   
EXEC @hr = sp_OAMethod @object, 'AddAttachment', NULL, @Attachment  
EXEC @hr = sp_OAMethod @object, 'Send', NULL  
  
--判断出错  
IF @hr <> 0  
BEGIN  
    EXEC sp_OAGetErrorInfo @object,@Body OUT,@Body OUT  
    PRINT '对不起，邮件发送失败！'  
    PRINT '错误信息：' + @Body  
END  
ELSE  
BEGIN  
    PRINT '恭喜您，邮件发送成功！  
END  
END