SQL Server 2005 邮件发送设置

Step 1:先创建好邮件配置文件

Step 2:

use msdb

--基本的发送语句
EXEC sp_send_dbmail @profile_name = 'Mail Profile Name',
@recipients = '邮件地址',
@subject = '我数据库邮件测试成功了!',
@body = 'hello,world!'

--包含查询的
EXEC sp_send_dbmail @profile_name = 'Mail Profile Name',
@recipients = '邮件地址',
@subject = '查询结果',
@query = 'select * from demo.dbo.orders',

--包含附件
EXEC sp_send_dbmail @profile_name = 'Mail Profile Name',
@recipients = '邮件地址',
@subject = '包含附件',
@body = '有附件，请查收',
@file_attachments = 'c:a.txt'

--将查询作为附件
EXEC sp_send_dbmail @profile_name = 'Mail Profile Name',
@recipients = '邮件地址',
@subject = '查询结果',
@body = '查询结果在附件中',
@query = 'select * from demo.dbo.orders',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'a.txt'

 

检查邮件发送情况

1.sysmail_help_queue_sp @queue_type = 'Mail'    检查邮件队列中未发送的邮件
   用法：http://technet.microsoft.com/zh-cn/library/ms190630.aspx

2.sysmail_delete_mailitems_sp 删除邮件队列中的邮件
   用法：http://technet.microsoft.com/zh-cn/library/ms190293.aspx