How to Programmatically Send Emails from SQL Server Using Stored Addresses?

How to Programmatically Send Emails from SQL Server Utilizing Stored Email Addresses

Sending emails from SQL Server can be a useful automation task for various scenarios. This article demonstrates how to achieve this using T-SQL when the email addresses are stored in a table.

Step-by-Step Implementation:

Step 1: Configure Database Mail

• Create a database mail profile and account using the Configure Database Mail Wizard. This wizard sets up the necessary infrastructure for sending emails.

Step 2: Enable Advanced Database Mail Features

Execute the following commands to enable advanced Database Mail functionality:

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

Step 3: Send a Single Email Using a Specific Profile

Use the sp_send_dbmail stored procedure to send an email from the specified profile:

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: Loop Through a Table and Send Emails

To send emails to multiple recipients based on a table of email addresses, follow these steps:

1. Declare variables for the email ID (@email_id), current ID (@id), maximum ID (@max_id), and SQL query (@query).
2. Get the minimum and maximum IDs from the [email_adresses] table.
3. Use a WHILE loop to iterate through the table IDs.
4. Within the loop, retrieve the email address (@email_id).
5. Dynamically build the sp_send_dbmail query based on the current email address.
6. Execute the query to send the email.
7. Update the current ID (@id) for the next iteration.

Here is the code for the 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

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