How To Send Mail Using SQL Server

HI sql developers ,

Few days ago , when I was working on my new blog , I was

thinking about a way to receive and email when a new row is inserted into a specific table in sql server, I was looking for a cheap way to do that , not only because I don’t have money , but also in my country there is no online payment system or cards to use SMTP service providers like SendGrid.

So when I was looking arround , I found out that I can send emails , using Sql Server ! really ? is this even possible ?

And everytime I got questions like that , I usually give it a try , because if you never try , you will never know “‘just don’t try walking in the dark when you move to a new house , believe me you will regret it “’ .

So to make this work , and for this article , we will explain how to configure sql-server to send email , and actually send one .

Ps : all the steps we are going to use are going to be using sql scripts (you can run those scripts even if you have no experience using sql scripts )

First we need to Create a Database Mail profile

profile

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = ‘Notifications’,
@description = ‘Profile used for sending outgoing notifications using Gmail.’ ;
GO

Next we need to Grant access to the profile to the DBMailUsers role

access

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = ‘Notifications’,
@principal_name = ‘public’,
@is_default = 1 ; GO

After that we need to Create a Database Mail account

account

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = ‘Gmail’,
@description = ‘Mail account for sending outgoing notifications.',
@email_address = ‘email@gmail.com’,
@display_name = ‘Automated Mailer’,
@mailserver_name = ‘smtp.gmail.com’, @port = 465, @enable_ssl = 1, @username = ‘Use a valid e-mail address’, @password = ‘email_password ;
GO

Final step is to Add the account to the profile

profile

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = ‘Notifications’,
@account_name = ‘Gmail’,
@sequence_number =1 ;
GO

Now we can try sending an email

send_dbmail

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'email@gmail.com',
    @recipients = 'recipientsemail@gmail.com',
   @body = 'The database mail configuration was completed successfully.',
     @subject = 'Automated Success Message';
   GO

To see all the configurations that you have on your sql server you can use :

SELECT *FROM msdb.dbo.sysmail_account
SELECT *FROM msdb.dbo.sysmail_configuration
SELECT *FROM msdb.dbo.sysmail_principalprofile
SELECT *FROM msdb.dbo.sysmail_profile
SELECT *FROM msdb.dbo.sysmail_profileaccount
SELECT *FROM msdb.dbo.sysmail_profileaccount