Below Information collected from peer blogs, and very usefull.
===============================================
To Enable Database
Mail execute the following block of code:
use master
go
exec sp_configure 'show advanced options', 1
reconfigure
exec sp_configure
'Database Mail XPs', 1
reconfigure
Deleting the Old
Profiles :
IF
EXISTS(
SELECT * FROM msdb.dbo.sysmail_profileaccount
pa JOIN msdb.dbo.sysmail_profile p
ON pa.profile_id = p.profile_id
JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id WHERE p.name = @ProfileName AND
a.name = @AccountName)
BEGIN
PRINT 'Deleting Profile Account'
EXECUTE sysmail_delete_profileaccount_sp @profile_name = @ProfileName, @account_name = @AccountName
END
IF
EXISTS(
SELECT * FROM msdb.dbo.sysmail_profile p WHERE
p.name = @ProfileName)
BEGIN
PRINT 'Deleting Profile.'
EXECUTE sysmail_delete_profile_sp @profile_name = @ProfileName
END
IF
EXISTS(
SELECT * FROM msdb.dbo.sysmail_account a WHERE
a.name = @AccountName)
BEGIN
PRINT
'Deleting Account.' EXECUTE sysmail_delete_account_sp @account_name = @AccountName
END
Creating Accounts
& Profiles for DB Mail:
--//
Create a Database Mail account
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'Test Mail Account',
@description = 'Mail account for administrative e-mail.',
@email_address = 'abc@xyz.com',
@replyto_address = 'abc@xyz.com',
@display_name = 'TEST Display',
@mailserver_name = 'smtp.xxxx.net',
@port
= 587,
@username = 'xyz',
@password = 'xxyyzz',
@enable_ssl = 1
-- Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = 'Test Mail Profile', @description = 'Profile used for
administrative mail.'
-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = 'Test Mail Profile', @account_name = 'Test Mail Account',
@sequence_number =1
-- Grant access to the profile to the DBMailUsers role
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'Test Mail Profile',
@principal_name = 'public', @is_default = 1
Sending Mail and
Check its status:
--Send
mail
EXEC msdb.dbo.sp_send_dbmail @recipients=N'abba@gmail.com',
@body= 'Test Email Body', @subject = 'Test Email Subject', @profile_name = 'Test Mail Profile'
--Send
mail with attachment
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DBMail'
,@recipients = 'GroupSQLDBA@MyCo.com'
,@from_address = 'DBMail@MyCo.com'
,@query
= 'SELECT resource_type, resource_database_id,
request_mode,
request_session_id
FROM sys.dm_tran_locks
WHERE request_mode IN (''IX'', ''X'')'
,@subject = 'Exclusive Locks'
,@attach_query_result_as_file = 1 ;
Important Tables used
in configuring Database mail and check their status:
--Profiles
SELECT * FROM msdb.dbo.sysmail_profile
--Accounts
SELECT * FROM msdb.dbo.sysmail_account
--Profile
Accounts
select * from msdb.dbo.sysmail_profileaccount
--Principal
Profile
select * from msdb.dbo.sysmail_principalprofile
--Mail
Server
SELECT * FROM msdb.dbo.sysmail_server
SELECT * FROM msdb.dbo.sysmail_servertype
SELECT * FROM msdb.dbo.sysmail_configuration
--Email
Sent Status
SELECT * FROM msdb.dbo.sysmail_allitems
SELECT * FROM msdb.dbo.sysmail_sentitems
SELECT * FROM msdb.dbo.sysmail_unsentitems
SELECT * FROM msdb.dbo.sysmail_faileditems
--Email
Status
SELECT SUBSTRING(fail.subject,1,25) AS
'Subject', fail.mailitem_id, LOG.descriptionFROM
msdb.dbo.sysmail_event_log LOG
join msdb.dbo.sysmail_faileditems fail ON
fail.mailitem_id = LOG.mailitem_id WHERE event_type = 'error'
--Mail Queues
EXEC msdb.dbo.sysmail_help_queue_sp
--DB
Mail Status
EXEC msdb.dbo.sysmail_help_status_sp
=====================================================================================
Examples
A. Sending an e-mail
message
This example sends an e-mail message to Dan Wilson using the
e-mail address danw@Adventure-Works.com. The message has the subject Automated
Success Message. The body of the message contains the sentence 'The stored
procedure finished successfully'.
EXEC msdb.dbo.sp_send_dbmail
@profile_name =
'Adventure Works Administrator',
@recipients =
'danw@Adventure-Works.com',
@body = 'The
stored procedure finished successfully.',
@subject =
'Automated Success Message' ;
B. Sending an e-mail
message with the results of a query
This example sends an e-mail message to Dan Wilson using the
e-mail address danw@Adventure-Works.com. The message has the subject Work Order
Count, and executes a
query that shows the number of work orders with a DueDate
less than two days after April 30, 2004. Database Mail attaches the result as a
text file.
EXEC msdb.dbo.sp_send_dbmail
@profile_name =
'Adventure Works Administrator',
@recipients =
'danw@Adventure-Works.com',
@query = 'SELECT
COUNT(*) FROM AdventureWorks2012.Production.WorkOrder
WHERE DueDate > ''2004-04-30''
AND DATEDIFF(dd, ''2004-04-30'',
DueDate) < 2' ,
@subject = 'Work
Order Count',
@attach_query_result_as_file = 1 ;
C. Sending an HTML
e-mail message
This example sends an e-mail message to Dan Wilson using the
e-mail address danw@Adventure-Works.com. The message has the subject Work Order
List, and contains an HTML document that shows the work orders with a DueDate
less than two days after April 30, 2004. Database Mail sends the message in
HTML format.
DECLARE @tableHTML
NVARCHAR(MAX) ;
SET @tableHTML =
N'
Work
Order Report
' +
N'
| Work Order ID | Product
ID | ' +
|---|
N'
Name | Order Qty | Due
Date | ' +
N'
Expected Revenue |
