How To Configure Database mail In SQL Server

introduction for database Mail in SQL Server 2012

Today I will explain how to configure database mail in SQL Server 2005,2008,2012 , No difference between this version in the database mail configuration , database Mail is good Option in SQL Server because very easy in the Configuration and its very helpful I think ALL DBA use this option because this option he can Follow up his database . if you want to configure Database Mail you have to ways one is the Wizard ant the Way no Two is Coding and i like this way more than the wizard so let me sow to you how can you Configure Database Mail in SQL Server 2012 by Coding *** I say SQL Server 2012 because when I write this Article i make this Example in my SQL Server 2012 ****

Configure Database mail In SQL Server Steps

Step No one [Enable database Mail option]

use master

go

sp_configure ‘show advanced options’,1

go

reconfigure with override

go

sp_configure ‘Database Mail XPs’,1  —— Enable database Mail

–go

–sp_configure ‘SQL Mail XPs’,0 ——- Disable database mail

go

reconfigure

go

Step No Two [Create database mail Account]

EXECUTE msdb.dbo.sysmail_add_account_sp

@account_name = ‘safeerp’,

@description = ‘Mail account for Database Mail For safeerp Company Created By Mr/ Mostafa Elmasry’,

@email_address = ‘Elmasre_201038@Yahoo.com‘,

@display_name = ‘Database Mail SQL Server 2012′,

@username=’Elmasre_201038@Yahoo.com’,

@password=’Email Password’,

@mailserver_name = ‘smtp.mail.yahoo.com’

 Step No Three [Create database mail profile]

EXECUTE msdb.dbo.sysmail_add_profile_sp

@profile_name = ‘safe’,

@description = ‘Profile used for database mail safeerp Company Created By Mr /Mostafa Elmasry’

Step No Four [Add the database mail account on the profile]

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

@profile_name = ‘safe’,

@account_name = ‘safeerp’,

@sequence_number = 1

Step No Five [Database mail profile Secuirty]

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

@profile_name = ‘safe’,

@principal_name = ‘public’,  ——- you can write here Puplic or Private (Puplic) any User in SQL Server have permision to use the database mail but (private) the User Create he only can use the database mail.

@is_default = 1 ;

Final Step No Sex [Send Email by DataBase Mail SQL Server]

declare @body1 varchar(100)

set @body1 = ‘Server :’+@@servername+ ‘ My First Database Email ‘

EXEC msdb.dbo.sp_send_dbmail @recipients=’elmasre_201038@hotmail.com’,

@subject = ‘My Mail Test From Daem DataBase Mail’,

@body = @body1,

@body_format = ‘HTML’ ;

Or

EXEC msdb.dbo.sp_send_dbmail

@profile_name = ‘safe’

,@recipients = ‘elmasre_201038@Hotmail.com’

,@from_address = ‘Elmasre_201038@Yahoo.com’

,@subject = ‘Mostafa sql server’

,@body=’My Friend Kemo  now i try to send new Email from my Databse mail Account’

,@query = ‘SELECT * FROM msdb..sysmail_allitems WHERE sent_status =”sent” ‘

/*

When you want to send mail like step no 6 you must learn the pramter to send it to the procedure [msdb.dbo.sp_send_dbmail] so lets check this pramter in this link http://msdn.microsoft.com/en-us/library/ms190307.aspx

*/

Now after you send the database mail sure you want to check the status for this mail so run tis script to check the mail status

SELECT * FROM msdb.dbo.sysmail_event_log

order by log_date

use msdb

exec sysmail_start_sp

GO

SELECT * FROM msdb..sysmail_allitems WHERE sent_status = ‘sent’