SQL2005邮件 发送
/*******************************************************************************************************************
邮件内容存储过程
********************************************************************************************************************/
use Blink_Star_PC
go
if exists(select * from sysobjects where Type='P' and name='SP_SendEmail')
begin
drop proc SP_SendEmail
end
go
create proc SP_SendEmail
(
@SendTo varchar(50)
)
as
begin
declare @count as int
set @count = (select count(玩具号) from GlobalToyz.dbo.玩具)
if (@count >= 20)
begin
--调用SQL2005的邮件发送的存储过程
EXEC msdb.dbo.sp_send_dbmail
--就是在步骤2中的配置文件名。--现有数据库邮件配置文件的名称 如果未指定 profile_name 默认专用配置文件使用 msdb 数据库的默认公共配置文件
@profile_name = 'Jerry',
--用来接受邮件的地址
@recipients = @SendTo,
-- 邮件主题
@subject = 'SQL server 发送邮件测试',
-- 邮件内容 类型为 nvarchar(max)，默认值为 NULL。
--@body = '这是 '+ convert(varchar(max),getdate(),120) +' 转入超过20次的用户信息：',
@body = '这是转入超过20次的用户信息：',
--邮件正文的格式 Text/Html 默认为 TEXT
@body_format =Text,
--要执行的查询。查询结果可以作为文件附加，或包含在电子邮件的正文中
@query ='SP_TO',
--存储过程在其中运行查询的数据库上下文 只有在指定 @query 时，此参数才适用。
@execute_query_database ='GlobalToyz',
--电子邮件附件的文件名列表，以分号分隔。必须使用绝对路径指定列表中的文件
@file_attachments ='f:\1.xlsx',
--用于设置查询结果的格式的线条宽度（字符） 数据类型为 int，默认值为 256 指定 @query 时，此参数才适用。
@query_result_width = 80,
--用于分隔查询输出中的列的字符。分隔符的类型为 char(1)。默认为 ' '（空格）
@query_result_separator =' ';
end
else
print '不发送！';
end
/*******************************************************************************************************************
邮件内容存储过程
********************************************************************************************************************/
use Blink_Star_PC
go
if exists(select * from sysobjects where Type='P' and name='SP_TO')
begin
drop proc SP_TO
end
go
create proc SP_TO
as
begin
select 玩具号,玩具名,价格,数量 from GlobalToyz.dbo.玩具
print char(10)+'这是转出超过20次的用户信息： '
select 定单号,定单日期,定单支付情况,总价格 from GlobalToyz.dbo.定单
end
/* 执行发送邮件功能
******************************************************************************************
use Blink_Star_PC
execute SP_SendEmail 'blink_star@QQ.com;blink_star@126.com',20
*******************************************************************************************/
/*******************************************************************************************************************
查询邮件信息
********************************************************************************************************************/
--查询发送信息
SELECT * FROM msdb.dbo.sysmail_allitems order by mailitem_id desc
--delete from msdb.dbo.sysmail_allitems
WHERE mailitem_id = 18
--邮件发送状态
select mailitem_id,sent_status from msdb.dbo.sysmail_allitems order by mailitem_id desc
WHERE mailitem_id =16
--邮件发送日志
SELECT * FROM msdb.dbo.sysmail_event_log order by Log_id desc
--delete from msdb.dbo.sysmail_event_log
WHERE mailitem_id =16
-------------------------------------------------------------------------
-----------------------------不明白的地方--------------------------------
-------------------------------------------------------------------------
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 f:\1.xls'
EXEC master..xp_cmdshell 'bcp 数据库名.表名 out c:\temp1.xls -c -q -S 服务名 -U sa -P 密码'
-------------------------------------------------------------------------
-----------------------------邮件附件发送--------------------------------
-------------------------------------------------------------------------
--开启 OLE Automation Procedures。
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
--定义邮件参数
--收件人
DECLARE @To AS varchar(500)
--SET @To = 'to@mailserver.com'
set @To='blink_star@126.com'
--发件人
DECLARE @From AS varchar(500)
--SET @From = ' from <mailto:from@mailserver.com%3E'
set @From='c2c_5151pk@sohu.com'
--主题
DECLARE @Subject AS varchar(500)
SET @Subject = 'Subject'
--内容
DECLARE @Body AS varchar(8000)
SET @Body =' select 玩具号,玩具名,价格,商标,数量 from GlobalToyz.dbo.玩具'
--SET @Body = 'From xue with auto sqlmail on '+convert(char(19),getdate(),121)+''
DECLARE @Attachment as varchar(250)
set @Attachment = 'f:\1.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%22).Value', '2'
--SMTP服务器地址
EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver%22).Value', 'smtp.sohu.com'
EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport%22).Value', '25'
--SMTP认证方式，为不需要认证，为明文密码认证，为SSL
EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate%22).Value', '1'
--邮箱地址或登陆用户名
EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername%22).Value', 'c2c_5151pk@sohu.com'
--邮箱密码
EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword%22).Value', 'c2c_5151pk'
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
/******************************************************************************
SQL 发送邮件 参数说明
******************************************************************************/
IF EXISTS(SELECT name from master..sysdatabases where name = 'SalesDB')
DROP DATABASE SalesDB
CREATE DATABASE SalesDB;
USE SALESDB;
CREATE TABLE SalesHistory
(
SaleID INT IDENTITY(1,1),
Product VARCHAR(30),
SaleDate SMALLDATETIME,
SalePrice MONEY
)
DECLARE @i SMALLINT
SET @i = 1
WHILE (@i <=10)
BEGIN
INSERT INTO SalesHistory (Product, SaleDate, SalePrice) VALUES ('Computer', DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57) )
INSERT INTO SalesHistory (Product, SaleDate, SalePrice) VALUES ('BigScreen', DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13) )
INSERT INTO SalesHistory (Product, SaleDate, SalePrice) VALUES ('PoolTable', DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29) )
SET @i = @i + 1
END
EXEC msdb.dbo.sp_send_dbmail
--收件人电子邮件地址列表，以分号分隔
@recipients=N'chapman.tim@gmail.com',
--收件人抄送邮件的电子邮件地址列表，以分号分隔
--@copy_recipients = null,
--收件人密件抄送邮件的电子邮件地址列表，以分号分隔
--@blind_copy_recipients = null,
--邮件主题
@subject ='Message Subject',
--电子邮件的正文 类型为 nvarchar(max)，默认值为 NULL。
@body='Message Body',
--现有数据库邮件配置文件的名称 如果未指定 profile_name 默认专用配置文件使用 msdb 数据库的默认公共配置文件
@profile_name =null,
--邮件正文的格式 Text/Html 默认为 TEXT
@body_format =Html,
--邮件的重要性 Low Normal High 默认值为 Normal
@importance = Normal,
--邮件的敏感度 Normal Personal Private Confidential 默认值为 Normal。
@sensitivity =Normal,
--电子邮件附件的文件名列表，以分号分隔。必须使用绝对路径指定列表中的文件
@file_attachments =null,
--要执行的查询。查询结果可以作为文件附加，或包含在电子邮件的正文中
@query ='SELECT Product FROM SalesHistory GROUP BY Product HAVING COUNT(*) > 3',
--存储过程在其中运行查询的数据库上下文 只有在指定 @query 时，此参数才适用。
--@execute_query_database ='',
--指定查询结果集是否作为附件返回 如果该值为 1，结果作为附件返回。只有在指定 @query 时，此参数才适用。
@attach_query_result_as_file = 1,
--指定查询结果集附件使用的文件名 为 0，则忽略此参数 为 1 且此参数为 NULL，则数据库邮件会创建任意文件名
@query_attachment_filename ='Results.txt',
--用于设置查询结果的格式的线条宽度（字符） 数据类型为 int，默认值为 256 指定 @query 时，此参数才适用。
@query_result_width = 10,
---[ @query_result_header = ] query_result_header
--指定查询结果是否包含列标题。query_result_header 值的数据类型为 bit。如果该值为 1，则查询结果包含列标题。如果该值为 0，则查询结果不包含列标题。该参数的默认值为 1。只有在指定 @query 时，此参数才适用。
--[ @query_result_width = ] query_result_width
--用于设置查询结果的格式的线条宽度（字符）。query_result_width 的数据类型为 int，默认值为 256。提供的值必须介于 10 到 32767 之间。只有在指定 @query 时，此参数才适用。
--[ @query_result_separator = ] 'query_result_separator'
---用于分隔查询输出中的列的字符。分隔符的类型为 char(1)。默认为 ' '（空格）。
--[ @exclude_query_output = ] exclude_query_output
--指定是否使用电子邮件返回查询执行的输出。exclude_query_output 的数据类型为 bit，默认值为 0。当此参数为 0 时，sp_send_dbmail 存储过程的执行将在控制台上打印作为查询执行结果而返回的消息。当此参数为 1 时，sp_send_dbmail 存储过程的执行不会在控制台上打印任何查询执行消息。
--[ @append_query_error = ] append_query_error
--指定是否在 @query 参数中指定的查询返回错误时发送电子邮件。exclude_query_output 的数据类型为 bit，默认值为 0。如果该参数的值为 1，数据库邮件会发送电子邮件，并将查询错误消息包含在电子邮件的正文中。如果该参数的值为 0，数据库邮件不发送电子邮件，sp_send_dbmail 在结束时会返回代码 1，表示失败。
--[ @query_no_truncate = ] query_no_truncate
--指定是否使用可避免截断大型可变长度数据类型（varchar(max)、nvarchar(max)、varbinary(max)、xml、text、ntext、image 以及用户定义数据类型）的选项执行查询。设置该选项后，查询结果将不包含列标题。query_no_truncate 值的数据类型为 bit。当该值为 0 或未指定时，查询中的列截断为 256 个字符。当该此值为 1 时，不截断查询中的列。该参数的默认值为 0。
--[ @mailitem_id = ] mailitem_id [ OUTPUT ]
--可选输出参数将返回消息的 mailitem_id。mailitem_id 的数据类型为 int。
/*
******************************************************************************************************************************************
发送邮件
******************************************************************************************************************************************/
*********** ********************************************************************
USE [msdb]
GO
print '开启数据库邮件服务选项'
exec sp_configure 'Database Mail XPS' , 1
RECONFIGURE ;
-- 启用数据库邮件功能
--EXEC sys.sp_configure N'show advanced options', 1
--RECONFIGURE;
--EXEC sys.sp_configure 'Database Mail XPs', 1;
--RECONFIGURE
print '启动数据库邮件服务'
exec sysmail_start_sp
go
/*
******************************************************************************************************************************************
配置邮件发送文件
******************************************************************************************************************************************/
print '开始配置数据库邮件'
DECLARE @account_id int , @profile_id int , @rc int
print '创建一个新的数据库邮件帐户，用于保存有关SMTP 帐户的信息。'
exec @rc = msdb . dbo . sysmail_add_account_sp @account_name = ' Project _dbagent' ,
@email_address = ' Project _dbagent@ 163.com ' ,
@display_name = ' Project _DbAgent mail' ,
@replyto_address = ' Project _DbAgent@ 163.com ' ,
@description = '数据库代理作业管理员' ,
@mailserver_name = ' mail.163.com ' ,
@mailserver_type = 'SMTP' , -- 区分大小写，只能填这个
@port = 25 ,
@username = ' Project _dbagent' ,
@password = ' Project job' ,
@use_default_credentials = 0 ,
@enable_ssl = 0 ,
@account_id = @account_id OUTPUT
select 'sysmail_add_account_sp' as name , @rc as [@rc] , @account_id as [@account_id]
print '创建新的数据库邮件配置文件。'
exec @rc = msdb . dbo . sysmail_add_profile_sp @profile_name = '数据库代理作业报告邮件配置' ,
@description = '数据库代理作业报告邮件配置' ,
@profile_id = @profile_id OUTPUT
select 'sysmail_add_profile_sp' as name , @rc as [@rc] , @profile_id as [@profile_id]
print '在数据库邮件配置文件中添加一个数据库邮件帐户。'
exec @rc = msdb . dbo . sysmail_add_profileaccount_sp
@profile_id = @profile_id , @account_id = @account_id , @sequence_number = 1
select 'sysmail_add_profileaccount_sp' as name , @rc as [@rc]
print '创建数据库代理的操作员，这里@email_address 要改为接收作业报告邮件的邮箱！'
EXEC msdb . dbo . sp_add_operator @name = N' Project _DbAgent' ,
@enabled = 1 ,
@weekday_pager_start_time = 0 ,
@weekday_pager_end_time = 235959 ,
@saturday_pager_start_time = 0 ,
@saturday_pager_end_time = 235959 ,
@sunday_pager_start_time = 0 ,
@sunday_pager_end_time = 235959 ,
@pager_days = 127 ,
@email_address = N' Project _DbAgent@ 163.com ' ,
@pager_address = N' Project _DbAgent@ 163.com '
--,@category_name=N'[DBA]'
if @@error != 0
print '数据库邮件配置完成！'
else
print '数据库邮件配置失败！'
GO
print '发送测试邮件！'
EXEC msdb . dbo . sp_send_dbmail
@profile_name = '数据库代理作业报告邮件配置' ,
@recipients = ' Project _dbagent@ 163.com ' ,
@body = '我的测试' ,
@subject = '数据库代理作业报告邮件配置测试'
--@attach_query_result_as_file = 1 ;
print ' 为SQL代理配置邮件服务 ！'
EXEC master . dbo . xp_instance_regwrite N'HKEY_LOCAL_MACHINE' , N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent' , N'DatabaseMailProfile' , N'REG_SZ' , N'开发测试邮件配置'
GO
-- 配置完成后，就可以为已有的作业配置邮件服务了。
USE [msdb]
GO
USE [msdb]
GO
-- 更新指定作业的警告选项
EXEC msdb . dbo . sp_update_job @job_name = N'xxxx' ,
@notify_level_email = 3 ,
@notify_level_netsend = 2 ,
@notify_level_page = 2 ,
@notify_email_operator_name = N' Project _DbAgent'
/*
******************************************************************************************************************************************
完整的发送邮件
******************************************************************************************************************************************/
-- =================================================
-- 启用数据库邮件功能
-- =================================================
EXEC sys.sp_configure N'show advanced options', 1
RECONFIGURE;
EXEC sys.sp_configure 'Database Mail XPs', 1;
RECONFIGURE;
-- =================================================
-- 配置
-- =================================================
DECLARE
@account_name sysname,
@profile_name sysname;
SELECT
@account_name = N'blinkstar129@gmail.com', -- 数据库邮件帐号
@profile_name = N'gmail'; -- 数据库邮件配置文件名
-- 数据库邮件帐号
EXEC msdb.dbo.sysmail_add_account_sp
@account_name = @account_name,
@email_address = N'blinkstar129@gmail.com', -- Email 地址
@display_name = N'gmail test', -- 回复地址
@mailserver_name = N'smtp.gmail.com', -- Gmail smtp 服务器地址
@port = 456, -- 端口号(对于Gmail, 如果 25 不通则改用 456/587)
@username = N'blinkstar129@gmail.com', -- Gmail 邮件地址
@password = N'715LOVE_129', -- Gmail 邮件帐号密码
@use_default_credentials = 0,
@enable_ssl = 1; -- 启用SSL 通讯加密
-- 数据库邮件配置文件
EXEC msdb.dbo.sysmail_add_profile_sp
@profile_name = @profile_name;
-- 把数据库邮件帐号添加到邮件配置文件中
EXEC msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = @profile_name,
@account_name = @account_name,
@sequence_number = 1;
-- 授予任意用户使用数据库邮件配置文件的权限
EXEC msdb.dbo.sysmail_add_principalprofile_sp
@principal_name = N'guest',
@profile_name = @profile_name,
@is_default = 0;
-- =================================================
-- 发送邮件
-- =================================================
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'gmail',
@recipients = N'cyx3925894@sina.com', -- 收件人地址
@subject = N'SQL server 发送邮件测试', -- 邮件主题
@body = N'SQL server 发送邮件测试成功', -- 邮件内容
@query ='SELECT top 10 name from Blink_Star_PC.dbo.city';
--邮件发送日志
SELECT * FROM msdb.dbo.sysmail_event_log order by Log_id desc
--邮件发送状态
select mailitem_id,sent_status from msdb.dbo.sysmail_allitems order by mailitem_id desc
-- =================================================
-- 删除配置
-- =================================================
/* --
DECLARE
@account_name sysname,
@profile_name sysname;
SELECT
@account_name = N'GmailTest@gmail.com',
@profile_name = N'gmail';
-- 从邮件配置文件中删除数据库邮件帐号
EXEC msdb.dbo.sysmail_delete_profileaccount_sp
@profile_name = @profile_name,
@account_name = @account_name;
-- 删除数据库邮件帐号
EXEC msdb.dbo.sysmail_delete_account_sp
@account_name = @account_name;
-- 删除数据库邮件配置文件
EXEC msdb.dbo.sysmail_delete_profile_sp
@profile_name = @profile_name;
-- 禁用数据库邮件功能
EXEC sys.sp_configure 'Database Mail XPs', 0;
RECONFIGURE;
EXEC sys.sp_configure N'show advanced options', 0
RECONFIGURE;
-- */
-- =================================================
-- 查询邮件发送信息
-- =================================================
--查询发送信息
SELECT * FROM msdb.dbo.sysmail_allitems order by mailitem_id desc
WHERE mailitem_id = 18
--邮件发送状态
select mailitem_id,sent_status from msdb.dbo.sysmail_allitems order by mailitem_id desc
WHERE mailitem_id =16
--邮件发送日志
SELECT * FROM msdb.dbo.sysmail_event_log order by Log_id desc
WHERE mailitem_id =16
浙公网安备 33010602011771号