Sending Email From SQL Server

Sending emails directly from SQL Server is a powerful capability, especially useful for automated reporting, alerting on critical database events, or notifying users about job statuses. This is accomplished through Database Mail, a reliable and secure feature built into the SQL Server engine.

In this article, you’ll learn:

• What Database Mail is
• How to configure Database Mail
• How to send an email using sp_send_dbmail
• Common use cases and troubleshooting tips

Prerequisites

Before setting up Database Mail, ensure:

• SQL Server Database Mail feature is installed (part of the Database Engine).
• The SQL Server Agent service is running.
• SMTP server details are available (host, port, credentials if needed).

Sending Email from SQL Server: A Complete Guide

Getting Started

SQL Server provides a powerful and secure feature called Database Mail that allows you to send email messages directly from the database engine. This capability is particularly valuable for automating reports, alerting administrators about critical issues, and notifying users about job statuses or data-related events.

What Is Database Mail?

Database Mail is a component in SQL Server that allows you to send emails using SMTP (Simple Mail Transfer Protocol). It supports HTML formatting, file attachments, and query result exports.

Unlike the deprecated SQL Mail, Database Mail does not require Outlook or a MAPI client.

Why Send Email from SQL Server?

Sending email from SQL Server can support:

• Automated Reporting: Send daily or weekly reports to stakeholders.
• Job Status Notifications: Alert admins on success or failure of SQL Server Agent Jobs.
• Database Alerts: Notify when thresholds are crossed or issues are detected.
• Audit Trails: Provide notification when sensitive data is changed.

SQL Server: Database Mail Setup

To set up Database Mail, you need to complete two steps: enabling Database Mail and configuring the mail profile.

Enable Database Mail

Before you can use Database Mail, it must be enabled. The following SQL query enables it.

 EXEC sp_configure 'show advanced options', 1;  
 RECONFIGURE;  
 EXEC sp_configure 'Database Mail XPs', 1;  
 RECONFIGURE;  

This enables the Database Mail extended stored procedures. The term "Database Mail extended" usually refers (either formally or informally) to the Database Mail Extended Stored Procedures in SQL Server, which are a set of system procedures that allow the SQL Server Database Engine to send email messages.

SQL Server : Configure Database Mail

Database Mail configuration in SQL Server can be accomplished in two ways, using SQL Server Management Studio (SSMS) or by executing T-SQL queries.

Using SSMS

1. Go to Object Explorer → Management → Database Mail.
2. Right-click → Configure Database Mail.
3. Choose Set up Database Mail.
4. Enter a profile name.
5. Create an SMTP account (you'll need SMTP server details, port, and credentials).
6. Assign the account to the profile.
7. Finish the wizard.

Make sure the SMTP server allows relay or has the proper authentication.

Using T-SQL

 -- Create an account  
 EXEC msdb.dbo.sysmail_add_account_sp  
   @account_name = 'MyMailAccount',  
   @description = 'Mail account for sending alerts',  
   @email_address = 'your_email@example.com',  
   @display_name = 'SQL Server Mailer',  
   @mailserver_name = 'smtp.yourdomain.com'; -- Add @port, @username, @password as needed  
 -- Create a profile  
 EXEC msdb.dbo.sysmail_add_profile_sp  
   @profile_name = 'MyMailProfile',  
   @description = 'Profile for SQL mail';  
 -- Add the account to the profile  
 EXEC msdb.dbo.sysmail_add_profileaccount_sp  
   @profile_name = 'MyMailProfile',  
   @account_name = 'MyMailAccount',  
   @sequence_number = 1;  

Sending Email From SQL Server

Once the profile is set, you can send emails easily.

 EXEC msdb.dbo.sp_send_dbmail  
   @profile_name = 'MyMailProfile',  
   @recipients = 'recipient@example.com',  
   @subject = 'SQL Server Alert',  
   @body = 'This is a test message sent from SQL Server.';  

Check the Database Mail Log under Management > Database Mail > View Database Mail Log for any errors or confirmations.

Troubleshooting Tips

• Check the Database Mail Log (sysmail_event_log in the msdb database) if messages aren’t sent.
• Make sure the SQL Server service account has internet access.
• Confirm SMTP server details, firewall rules, and authentication credentials.
• Verify the mail profile is correctly set and active.

View Mail log:

 SELECT * FROM msdb.dbo.sysmail_event_log ORDER BY log_date DESC;  

Summary

Sending email from SQL Server using Database Mail is powerful and relatively easy to configure. Whether you need to deliver automated reports or alert your team of critical events, Database Mail provides a robust framework directly within SQL Server.

By following the steps above, you’ll be equipped to integrate email notifications into your SQL Server workflows efficiently. I hope you found this information helpful.

Thanks