select * from msdb.dbo.sysmail_sentitems
select sent_date,
send_request_user,
subject,
body
from msdb.dbo.sysmail_sentitems
order by sent_date desc
-- This contains one row for each message that has an unsent or retrying status.
select * from msdb.dbo.sysmail_unsentitems
select sent_date,
send_request_user,
subject,
body
from msdb.dbo.sysmail_unsentitems
order by sent_date desc
-- This contains one row for each Database Mail message that has a failed status.
select * from msdb.dbo.sysmail_faileditems
select sent_date,
send_request_user,
subject,
body
from msdb.dbo.sysmail_faileditems
order by sent_date desc
---------------------------------------------------
-- msdb.dbo.sp_send_dbmail
---------------------------------------------------
DELETE FROM msdb.dbo.sysmail_sentitems
---------------------------------------------------
-- One recipient
---------------------------------------------------
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DefaultMailProfile',
@recipients = 'ram.kdm@gmail.com' ,
@body = 'Mail Test 1',
@subject = 'Automated Mail Message 1' ;
select TOP 1 sent_date, send_request_user, subject, body
from msdb.dbo.sysmail_sentitems
order by sent_date desc
---------------------------------------------------
-- More than one recipient
---------------------------------------------------
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DefaultMailProfile',
@recipients = 'ram.kdm@gmail.com ; ramk@naya-tech.co.il' ,
@body = 'Mail Test 2',
@subject = 'Automated Mail Message 2' ;
select TOP 1 sent_date, send_request_user, subject, body
from msdb.dbo.sysmail_sentitems
order by sent_date desc
---------------------------------------------------
-- Unavailable Mail Address
---------------------------------------------------
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DefaultMailProfile',
@recipients = 'ram.kdm@gmail.com ; ramk@naya-tech.co.il ; MAILSHELOKAYAM@RAM.COM' ,
@body = 'Mail Test 3',
@subject = 'Automated Mail Message 3' ;
select TOP 1 sent_date, send_request_user, subject, body
from msdb.dbo.sysmail_sentitems
order by sent_date desc
---------------------------------------------------
-- @copy_recipients
---------------------------------------------------
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DefaultMailProfile',
@recipients = 'ram.kdm@gmail.com' ,
@copy_recipients = 'ramk@naya-tech.co.il',
@body = 'Mail Test 4',
@subject = 'Automated Mail Message 4' ;
select TOP 1 sent_date, send_request_user, subject, body
from msdb.dbo.sysmail_sentitems
order by sent_date desc
---------------------------------------------------
-- @blind_copy_recipients (hidden mail recipient)
---------------------------------------------------
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DefaultMailProfile',
@recipients = 'ram.kdm@gmail.com' ,
@blind_copy_recipients = 'ramk@naya-tech.co.il',
@body = 'Mail Test 5',
@subject = 'Automated Mail Message 5' ;
select TOP 1 sent_date, send_request_user, subject, body
from msdb.dbo.sysmail_sentitems
order by sent_date desc
---------------------------------------------------
-- @body_format as HTML
---------------------------------------------------
DECLARE @html_message varchar(500) =
'<!DOCTYPE html>
<html>
<body>
<h1>Some Heading</h1>
<p>Some paragraph.</p>
</body>
</html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DefaultMailProfile',
@recipients = 'ram.kdm@gmail.com' ,
@blind_copy_recipients = 'ramk@naya-tech.co.il',
@body = @html_message,
@body_format= 'HTML',
@subject = 'Automated HTML Mail Message 6' ;
select TOP 1 sent_date, send_request_user, subject, body
from msdb.dbo.sysmail_sentitems
order by sent_date desc
---------------------------------------------------
-- @file_attachments
---------------------------------------------------
-- * semicolon-delimited list of file names to attach to the e-mail message.
-- * Files in the list must be specified as absolute paths.
-- * The attachments list is of type nvarchar(max).
-- * By default, Database Mail limits file attachments to 1 MB per file.
EXECUTE msdb.dbo.sysmail_configure_sp 'MaxFileSize', '2097152' ; --size is in bytes = 2MB
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DefaultMailProfile',
@recipients = 'ram.kdm@gmail.com' ,
@body = 'Mail Test',
@subject = 'Automated Mail Message 7',
@file_attachments= 'C:DBMailFileToSend.txt'
select TOP 1 sent_date, send_request_user, subject, body
from msdb.dbo.sysmail_sentitems
order by sent_date desc
---------------------------------------------------
-- @query (Plain Text)
---------------------------------------------------
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DefaultMailProfile',
@recipients = 'ram.kdm@gmail.com' ,
@body = 'Mail Test 7',
@subject = 'Automated Mail Message 8',
@query= 'SELECT * FROM Northwind.dbo.employees'
select TOP 1 sent_date, send_request_user, subject, body
from msdb.dbo.sysmail_sentitems
order by sent_date desc
---------------------------------------------------
-- @query (As HTML)
---------------------------------------------------
-- 1. Use the following script : SaveTableAsHTML
http://www.virtualobjectives.com.au/sqlserver/saving_to_html.htm
-- 2. SaveTableAsHTML Basic Demo
EXECUTE northwind.dbo.SaveTableAsHTML @DBFetch = 'select * from Northwind.dbo.employees'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DefaultMailProfile',
@recipients = 'ram.kdm@gmail.com',
@subject = 'HTML Formated Query 1',
@body_format = 'HTML',
@query = 'EXECUTE northwind.dbo.SaveTableAsHTML
@DBFetch = ''select * from Northwind.dbo.employees'''
---------------------------------------------------
-- @query (As HTML)
---------------------------------------------------
-- 1. Use the following script : SaveTableAsHTML
http://www.virtualobjectives.com.au/sqlserver/saving_to_html.htm
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DefaultMailProfile',
@recipients = 'ram.kdm@gmail.com',
@subject = 'HTML Formated Query 2',
@body_format = 'HTML',
@query = 'EXECUTE northwind.dbo.SaveTableAsHTML
@DBFetch = ''SELECT categoryID , AVG(unitPrice) AS AvgPrice
FROM products
WHERE categoryID <> 3
GROUP BY categoryID
Having AVG(unitPrice) > 15 '''
select TOP 1 sent_date, send_request_user, subject, body
from msdb.dbo.sysmail_sentitems
order by sent_date desc
---------------------------------------------------
-- Using CSS ...
---------------------------------------------------
-- Demo
EXEC SaveTableAsHTML @DBFetch = 'SELECT * FROM northwind.dbo.products WHERE unitPrice > 20',
@CSS =
'table{font-family:"Lucida Sans Unicode", "Lucida Grande", Sans-Serif;font-size:12px;width:480px;text-align:left;border-collapse:collapse;margin:20px;}
th{font-size:13px;font-weight:bold;background:#b9c9fe;border-top:4px solid #aabcfe;border-bottom:1px solid #fff;color:black;padding:8px;}
td{background:#e8edff;border-bottom:1px solid #fff;color:#669;border-top:1px solid transparent;padding:8px;}
tr:hover td{background:#d0dafd;color:#339;}'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DefaultMailProfile',
@recipients = 'ram.kdm@gmail.com',
@subject = 'HTML Formated Query 3',
@body_format = 'HTML',
@query =
'EXEC Northwind.dbo.SaveTableAsHTML @DBFetch = ''SELECT * FROM northwind.dbo.products WHERE unitPrice > 20'',
@CSS =
''table{font-family:"Lucida Sans Unicode", "Lucida Grande", Sans-Serif;font-size:12px;width:480px;text-align:left;border-collapse:collapse;margin:20px;}
th{font-size:13px;font-weight:bold;background:#b9c9fe;border-top:4px solid #aabcfe;border-bottom:1px solid #fff;color:black;padding:8px;}
td{background:#e8edff;border-bottom:1px solid #fff;color:#669;border-top:1px solid transparent;padding:8px;}
tr:hover td{background:#d0dafd;color:#339;}'''
select TOP 1 sent_date, send_request_user, subject, body
from msdb.dbo.sysmail_sentitems
order by sent_date desc
---------------------------------------------------
-- @attach_query_result_as_file
-- @query_attachment_filename
---------------------------------------------------
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DefaultMailProfile',
@recipients = 'ram.kdm@gmail.com',
@subject = 'HTML Formated Query 4',
@body_format = 'HTML',
@query =
'EXEC Northwind.dbo.SaveTableAsHTML @DBFetch = ''SELECT * FROM northwind.dbo.products WHERE unitPrice > 20'',
@CSS =
''table{font-family:"Lucida Sans Unicode", "Lucida Grande", Sans-Serif;font-size:12px;width:480px;text-align:left;border-collapse:collapse;margin:20px;}
th{font-size:13px;font-weight:bold;background:#b9c9fe;border-top:4px solid #aabcfe;border-bottom:1px solid #fff;color:black;padding:8px;}
td{background:#e8edff;border-bottom:1px solid #fff;color:#669;border-top:1px solid transparent;padding:8px;}
tr:hover td{background:#d0dafd;color:#339;}''',
@attach_query_result_as_file = 1 ,
@query_attachment_filename = 'SQLReport.html'
select TOP 1 sent_date, send_request_user, subject, body
from msdb.dbo.sysmail_sentitems
order by sent_date desc
