For an instructor lead, in-depth look at learning SQL click below.
L Server Database Mail: Configuring Email Notifications
SQL Server Database Mail is a versatile feature enabling you to send query results, operator notifications, and various other messages from your SQL Server instance to any email recipient. Configuring email notifications can seem daunting at first, but with a solid understanding of SQL Server Database Mail feature, the process becomes as easy as pie. Let’s work through that today.
Preconditions
Before starting with the configuration, ensure that you have the necessary permissions. As a minimum, you need to have CONTROL SERVER permissions or be a member of the sysadmin fixed server role.
Deploying Database Mail
Deploying Database Mail involves three steps: configuration, sending test mail and managing Database Mail. We’ll start with configuring Database Mail.
Database Mail Configuration
We’ll use the “Database Mail Configuration Wizard”, available in SQL Server Management Studio, to set up the feature. Begin by enabling Database Mail as shown below:
html
|
1 2 3 4 5 6 |
EXEC sp_configure 'show advanced', 1; RECONFIGURE; EXEC sp_configure 'Database Mail XPs', 1; RECONFIGURE; |
This series of TSQL commands will enable the advanced options and turn on the Database Mail extended procedure.
Then, it’s time to setup your mail profile and account which will be used to handle email notifications. Fill the fields as shown with your custom data.
Here is an example of how you could set a profile:
html
|
1 2 3 4 5 6 |
EXECUTE m<a href="mailto:sdb.dbo.sysmail_add_profile_sp @profile_name" >sdb.dbo.sysmail_add_profile_sp @profile_name</a> = 'EmailProfile', @description = 'Mail profile for sending DB Notifications'; |
And setting an account using this profile would look like:
html
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
EXECUTE m<a href="mailto:sdb.dbo.sysmail_add_account_sp @account_name" >sdb.dbo.sysmail_add_account_sp @account_name</a> = 'EmailAccount', @email_address = <a href="mailto:'noreply@yoursite.com'" >'noreply@yoursite.com'</a>, @replyto_address = <a href="mailto:'noreply@yoursite.com'" >'noreply@yoursite.com'</a>, @display_name = 'DB Notifications', @mailserver_name = 'smtp.yoursite.com', @username='Username', @password='Password', @port=587, @use_default_credentials=0, @enable_ssl=1; |
Sending Test Mail
You can test Database Mail by sending test email using the profile. The code below sends a test email:
html
|
1 2 3 4 5 6 7 8 |
EXEC m<a href="mailto:sdb.dbo.sp_send_dbmail @profile_name='EmailProfile'" >sdb.dbo.sp_send_dbmail @profile_name='EmailProfile'</a>, @<a href="mailto:recipients='test@yoursite.com'" >recipients='test@yoursite.com'</a>, @body='The email body text.', @subject='Database Mail Test'; |
Managing Database Mail
Once Database Mail is set up and running, you can view and manage the mail items and Database Mail configuration using T-SQL commands or Management Studio.
Remember, proper setup and management of the Database Mail feature can ensure that your SQL Server is able to communicate effectively with your team. Happy coding!