fbpx
Contact Us

SQL Server – DatabaseMail Implementations

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

UpScale Analytics is one of the largest platforms in the world for learning SQL by doing, consisting over 300 SQL exercises at different levels (including solutions), by topics, across over 100 different datasets. More…

About Us

UpScale Analytics specializes in training solutions for organizations and individuals in the fields of data.

 UpScale Analytics is also one of the largest platforms in the world for learning SQL by doing.

Read more…

Facebook Youtube Linkedin

Popular Courses

Quick Links

Upscale Analytics 2022 © All Rights Reserved