22,210
社区成员
发帖
与我相关
我的任务
分享
use msdb
GO
DECLARE @mailitemid INT
EXEC sp_send_dbmail --调用SQL2005的邮件发送的存储过程。
@profile_name = 'mail_first', --邮件配置文件名。--调用发送邮件的存储过程,登录帐号必须有msdb的datamail权限
@recipients ='wangyunliang@yuantel.com',--用来接受邮件的地址
@subject = '标题',
@body = 'Hello,world',
@body_format='text' ,
@file_attachments ='', --附件
@mailitem_id = @mailitemid OUTPUT
SELECT @mailitemid --取得@mailitemid
WAITFOR DELAY '00:00:05' --等5秒后看结果
SELECT
er.log_id AS [LogID],
er.event_type AS [EventType],
er.log_date AS [LogDate],
er.description AS [Description],
er.process_id AS [ProcessID],
er.mailitem_id AS [MailItemID],
er.account_id AS [AccountID],
er.last_mod_date AS [LastModifiedDate],
er.last_mod_user AS [LastModifiedUser]
FROM msdb.dbo.sysmail_event_log er
WHERE mailitem_id = @mailitemid
ORDER BY [LogDate] DESC
USE msdb ;
GO
SELECT items.subject,
items.last_mod_date
,l.description FROM dbo.sysmail_faileditems as items
INNER JOIN dbo.sysmail_event_log AS l
ON items.mailitem_id = l.mailitem_id
WHERE items.recipients LIKE '%danw%'
OR items.copy_recipients LIKE '%danw%'
OR items.blind_copy_recipients LIKE '%danw%'
GO
use msdb
GO
EXEC sp_send_dbmail --调用SQL2005的邮件发送的存储过程。
@profile_name = 'mailadmin', --邮件配置文件名。--调用发送邮件的存储过程,登录帐号必须有msdb的datamail权限
@recipients ='name1@163.com;name2@163.com',--用来接受邮件的地址
@subject = '标题',
@body = 'Hello,world',
@body_format='text' ,
@file_attachments ='c:\zou.txt' --附件
--由于发送是异步进行的,这个存储过程只管发
--如果想知道发送的结果,只有查询邮件的发送日志了
SELECT
er.log_id AS [LogID],
er.event_type AS [EventType],
er.log_date AS [LogDate],
er.description AS [Description],
er.process_id AS [ProcessID],
er.mailitem_id AS [MailItemID],
er.account_id AS [AccountID],
er.last_mod_date AS [LastModifiedDate],
er.last_mod_user AS [LastModifiedUser]
FROM msdb.dbo.sysmail_event_log er
ORDER BY [LogDate] DESC