Today, we will be discussing an important feature of SQL Server called Database Mail. Database Mail is a powerful tool that allows us to send emails directly from SQL Server. In this article, we will go through the steps required to set up and use Database Mail.

Step 1: Create Profile and Account

The first step in using Database Mail is to create a profile and account. This can be done using the Configure Database Mail Wizard, which can be accessed from the Configure Database Mail context menu of the Database Mail node in the Management Node. The wizard allows us to manage accounts, profiles, and Database Mail global settings.

Step 2: Configure Email

Once the account and profile are created successfully, we need to configure the Database Mail. To do this, we need to enable the Database Mail XPs parameter through the sp_configure stored procedure. Here is an example:

sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO

Step 3: Send Email

After all the configurations are done, we are now ready to send an email. To send mail, we need to execute the stored procedure sp_send_dbmail and provide the required parameters. Here is an example:

USE msdb
GO
EXEC sp_send_dbmail
    @profile_name = 'PinalProfile',
    @recipients = 'test@example.com',
    @subject = 'Test message',
    @body = 'This is the body of the test message. Congratulations! Database Mail received by you successfully.'

Once the parameters are validated, certain stored procedures are executed and the mail is queued by Service Broker. Database Mail keeps copies of outgoing email messages and displays them in the sysmail_allitems, sysmail_sentitems, sysmail_unsentitems, and sysmail_faileditems tables. The status of the sent mail can be seen in the sysmail_mailitems table. A value of 1 in the sent_status field indicates a successful send, while a value of 2 indicates a failed send, and a value of 3 indicates an unsent email.

You can also check the log in the sysmail_log table to get more information about the email sending process.

After sending the email, you can check your inbox to see if the email has been received successfully.

Database Mail is a powerful feature that can be used for various purposes, such as sending notifications, reports, or alerts directly from SQL Server. It provides a convenient way to integrate email functionality into your database applications.

That’s all for today’s tutorial on Database Mail in SQL Server. I hope you found this article helpful. If you have any questions or feedback, please let me know in the comments below.