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
SQL Server 定时发送带附件的邮件
最新推荐文章于 2024-10-09 11:10:03 发布