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
--//
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'' +
N'
Work Order ID Product
ID ' +
N'Name Order Qty Due
Date ' +
N'Expected Revenue
CAST ( ( SELECT td
= wo.WorkOrderID, '',
td
= p.ProductID, '',
td
= p.Name, '',
td
= wo.OrderQty, '',
td = wo.DueDate, '',
td
= (p.ListPrice - p.StandardCost) * wo.OrderQty
FROM
AdventureWorks.Production.WorkOrder as wo
JOIN
AdventureWorks.Production.Product AS p
ON
wo.ProductID = p.ProductID
WHERE
DueDate > '2004-04-30'
AND
DATEDIFF(dd, '2004-04-30', DueDate) < 2
ORDER BY
DueDate ASC,
(p.ListPrice - p.StandardCost) * wo.OrderQty DESC
FOR XML
PATH('tr'), TYPE
) AS NVARCHAR(MAX)
) +
N'
'
;
EXEC msdb.dbo.sp_send_dbmail
@recipients='danw@Adventure-Works.com',
@subject = 'Work
Order List',
@body =
@tableHTML,
@body_format =
'HTML' ;
Below Code We can use to send test email from all existing DB PROFILES
USE msdb
GO
DECLARE @Total int
,@cnt int ,@ProfileName varchar(50)
,@Server varchar(50) ,@Sub varchar(400)
SET @Total = (SELECT MAX(profile_id) FROM
sysmail_profile)
SET @cnt = 1
SET @Server =
@@SERVERNAME
WHILE(@cnt <=
@Total)
BEGIN
SET
@ProfileName = (select name from sysmail_profile where profile_id = @cnt)
SET @Sub = 'Testing Email From ' + @Server + '
from profile : ' + @ProfileName
IF
(@ProfileName is not null)
BEGIN
PRINT @ProfileName
PRINT
@Sub
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'Test@gmail.com'
,
@subject = @Sub
, @body
= @Sub
,
@profile_name = @ProfileName
,
@body_format = 'HTML' ;
END
SET @cnt = @cnt + 1
END