Friday, November 14, 2008

Configure MSSQL database mail

MSSQL 2005 Enterprise or Standard versions provide mail feature integrated in the database, so that one can send bulk [:)] emails from the database.

Step 1

One should enable Database mail on the server, before setting up the Database Mail profile and accounts. Either can be done by using Transact SQL to enable Database Mail or the second method to use a GUI.
In the SQL Server Management Studio 2005, run the following statement.

use master
sp_configure 'show advanced options',1
reconfigure with override
sp_configure 'Database Mail XPs',1
--sp_configure 'SQL Mail XPs',0

Step 2
One can enable the Configuration Component Database account by using the sysmail_add_account procedure.
You’d execute the below query.

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'TestMailAccount',
@description = 'Mail account for Database Mail',
@email_address = '',
@display_name = 'MyAccount',
@mailserver_name = ''

Step 3
Now one should create a Mail profile.
You’d execute the below query.

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'TestMailProfile',
@description = 'Profile needed for database mail'

Step 4

Next will be the sysmail_add_profileaccount procedure, to include the Database Mail account which is created in step 2, along with the Database Mail profile in step 3.
You’d execute the below query.

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'TestMailProfile',
@account_name = 'TestMailAccount',
@sequence_number = 1

Step 5
You’d execute the below query.

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'TestMailProfile',
@principal_name = 'public',
@is_default = 1 ;

Step 6
After all these settings done, try to send a test mail from MSSQL Server.
You’d execute the below query.

declare @body1 varchar(100)
set @body1 = 'Server :'+@@servername+ ' Test DB Email '
EXEC msdb.dbo.sp_send_dbmail @recipients='',
@subject = 'Test',
@body = @body1,
@body_format = 'HTML' ;

Step 7
You’d configure the Database Mail profile and its account using MSSQL Server Management Studio by right click Database Mail > Configuration.

Step 8

You can review the logs linked to Database Mail.
You’d execute the below query.

SELECT * FROM msdb.dbo.sysmail_event_log


Anonymous said...

Hii Tanmay... read ur comment on Ruhi's blog abt u being from Morachi Chincholi. I'm Dhaval Ramtirthkar. I and my wife am planning to visit ur beautiful and unique village. Can you please guide us? We are staying in Mumbai but I'm basically from Pune... pakka 'Puneri' ahe :) So tell me Morachya Chincholi la aata December madhye aala tar 'mor' disaychi shakyata kiti? ki pavsalyanantarach yayla pahije?

'Mor' aapla rashtriya pakshi ahe pan laajirvani goshta ahe ki samanya lokanna 'mor' baghun mahitich nahi.

My mail id is Would be great if you can mail me. We would love to visit Morachi Chincholi.


Jamal's Corner said...


I don't have the configuration tool shown at STEP 7. why do you think that is . I have sql server 11

pramod100 said...

Nice link working fine expect one thing needs to add in step 2

"@enable_ssl = true", Once you add that value email will sent easily else it won't