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
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
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
EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = ‘Gmail’, @description = ‘Mail account for sending outgoing notifications.', @email_address = ‘email@example.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
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = ‘Notifications’, @account_name = ‘Gmail’, @sequence_number =1 ; GO
Now we can try sending an email
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'firstname.lastname@example.org', @recipients = 'email@example.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
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 =)
Leave a Reply