How Can I Send Emails from SQL Server Using Stored Email Addresses?

Sending Emails from SQL Server with Stored Email Addresses

Empowering your SQL Server database to send emails is an essential task in various scenarios. Suppose you have email addresses stored in a table and need to trigger email notifications. This article demonstrates a comprehensive approach to achieve this by utilizing the T-SQL programming language.

Step 1: Configure Database Mail Settings

Before sending emails, you must set up your database mail infrastructure. Use the Configure Database Mail Wizard in SQL Server Management Studio to create profiles, accounts, and global settings. This will enable email delivery from within your SQL Server environment.

Step 2: Enabling Extended Stored Procedures and Database Mail

To use the necessary stored procedures for sending emails, run the following commands:

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

Step 3: Sending a Single Email

To send a single email with a hardcoded email address, use the sp_send_dbmail procedure:

USE msdb
GO
EXEC sp_send_dbmail @profile_name='yourprofilename',
@recipients='[email protected]',
@subject='Test message',
@body='This is the body of the test message.
Congrates Database Mail Received By you Successfully.'

Step 4: Looping through a Table of Email Addresses

To send emails to multiple recipients stored in a table, you can iterate through the table using a while loop:

DECLARE @email_id NVARCHAR(450), @id BIGINT, @max_id BIGINT, @query NVARCHAR(1000)

SELECT @id=MIN(id), @max_id=MAX(id) FROM [email_adresses]

WHILE @id<=@max_id
BEGIN
    SELECT @email_id=email_id 
    FROM [email_adresses]

    set @query='sp_send_dbmail @profile_name=''yourprofilename'',
                        @recipients='''+@email_id+''',
                        @subject=''Test message'',
                        @body=''This is the body of the test message.
                        Congrates Database Mail Received By you Successfully.'''

    EXEC @query
    SELECT @id=MIN(id) FROM [email_adresses] where id>@id

END

This comprehensive approach allows you to efficiently send emails from SQL Server, regardless of whether your recipients' email addresses are stored in a table.

The above is the detailed content of How Can I Send Emails from SQL Server Using Stored Email Addresses?. For more information, please follow other related articles on the PHP Chinese website!