请问怎么用存储过程来发邮件?
以下执行成功但接收不到邮件 --------------------------------------------
alter PROCEDURE [dbo].[job_AutoMail_xxd]
---exec job_AutoMail_xxd
AS
BEGIN
SET NOCOUNT ON; --获取数据
--select * from northwind.dbo.bgroup
--drop table bgroup
--select * from northwind.dbo.new
truncate table northwind.dbo.bgroup
insert bgroup (new_title)
select new_title from new--(Link_Name,'exec store_procedure_name') exec master..xp_cmdshell 'del c:\*.xls'
EXEC master..xp_cmdshell 'bcp northwind.dbo.bgroup out c:\temp1.xls -c -q -S"hp" -U"sa" -P"0755"' --定义邮件参数
DECLARE @To AS varchar(500)
SET @To = '[email protected]'
--SET @To = '[email protected]' DECLARE @From AS varchar(500)
SET @From = ' from <[email protected]>'
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.163.com'
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', '用户名隐藏'
--邮箱密码
EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").Value', '密码隐藏' 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
以下执行成功但接收不到邮件 --------------------------------------------
alter PROCEDURE [dbo].[job_AutoMail_xxd]
---exec job_AutoMail_xxd
AS
BEGIN
SET NOCOUNT ON; --获取数据
--select * from northwind.dbo.bgroup
--drop table bgroup
--select * from northwind.dbo.new
truncate table northwind.dbo.bgroup
insert bgroup (new_title)
select new_title from new--(Link_Name,'exec store_procedure_name') exec master..xp_cmdshell 'del c:\*.xls'
EXEC master..xp_cmdshell 'bcp northwind.dbo.bgroup out c:\temp1.xls -c -q -S"hp" -U"sa" -P"0755"' --定义邮件参数
DECLARE @To AS varchar(500)
SET @To = '[email protected]'
--SET @To = '[email protected]' DECLARE @From AS varchar(500)
SET @From = ' from <[email protected]>'
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.163.com'
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', '用户名隐藏'
--邮箱密码
EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").Value', '密码隐藏' 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
@profile_name = 'XXXX',
@recipients = '[email protected]',
@body = 'XXXX',
@subject = 'XXXX' ;
declare @email varchar(60)
SET @email='邮件发送部分'
exec master..xp_sendmail '[email protected]',@email