In this blog post, we will explore how to send an email directly through SQL Server. This feature can be particularly useful for sending out email notifications for system alerts, such as when the temporary space reaches a specific threshold or when the transaction log is 90% full. We will walk through the process of setting up the database email configuration, creating a database profile, configuring the mail account, adding the email account to the profile, granting access to the database profile, enabling the database mail functionality, and finally, sending the email. Let's get started!
Step 1 - Setting up the Database Email Configuration
The first step is to create a database profile that will be used to send out the email notifications.
To do this, we will use the stored procedure "`msdb.dbo.sp_add_profile`". We need to pass the profile name and the description as parameters.
For example, we can set the profile name as "Knowstar" and the description as "system notifications".
Step 2 - Configuring the Mail Account
Next, we need to create or configure the mail account.
We will use the stored procedure "`msdb.dbo.sp_add_account`". We need to pass the account name, email address, mail server, port number, enable SSL, username, and password as parameters.
For example, we can set the account name as "SQL email account", the email address as "learn.knowstar@gmail.com", the mail server as "smtp.gmail.com", and so on.
Step 3 - Adding the Email Account to the Profile
Once the mail account is configured, we need to add it to the profile we created.
We will use the stored procedure "`msdb.dbo.sp_add_profileaccount`". We need to pass the profile name, account name, and sequence number as parameters. The sequence number determines which database mail account to use for new mail.
Step 4 - Granting Access to the Database Profile
Next, we need to grant access to the database profile to a particular database user or role. We can have either private access or public access.
For our example, we will grant public access. We will use the stored procedure "`msdb.dbo.sp_add_principalprofile`". We need to pass the profile name, principal ID, and is default as parameters. Setting the is default parameter to 1 means that we have declared it as the default profile for sending the emails.
Step 5 - Enabling the Database Mail Functionality
After setting up the database profile, we need to enable the database mail functionality in SQL Server.
We will make a call to the system stored procedure "`sp_configure`". We will set the "`show advanced options`" to 1, as the database mail functionality is an advanced option. We will then reconfigure and call the "`sp_configure`" procedure again, this time setting the "`database mail XPs`" to 1.
Step 6 - Sending the Email
Finally, we are ready to send the email.
We will use the stored procedure "`msdb.dbo.sp_send_dbmail`". We need to pass the profile name, recipient's email address, body of the message, and subject as parameters.
For example, we can set the profile name as "Knowstar", the recipient's email address as the desired email address, the body of the message as "testing", and the subject as "test system notification".
This feature can be particularly useful for sending out email notifications for system alerts. By following these steps, you can easily implement email notifications in your SQL Server environment. Thank you for reading!