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
account
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
tip
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
In the next article we will see how to trigger this ,when a new row is inserted , an email will be sent .
You can read more about sp_send_dbmail from Microsoft official documentation :
Happy SQL day =)