How Can I Automate Email Sending from SQL Server Using T-SQL?

Automatic Email Sending from SQL Server

This question seeks a solution for sending emails from SQL Server using Transact-SQL (T-SQL), specifically when the recipient email addresses are stored in a database table. The goal is to automate the email sending process by looping through the table and sending emails to each address.

Solution Steps:

1. Configuration:

• Create a profile and account using the Configure Database Mail Wizard. This wizard allows you to manage accounts, profiles, and global settings for Database Mail.

• Set up SQL Server permissions by running the following commands:

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

  Copy after login

2. Sending Emails:

• To send emails programmatically, use the sp_send_dbmail stored procedure:

  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.'

  Copy after login

3. Looping Through a Table:

• To send emails to multiple recipients stored in a table, use a 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

  Copy after login

The above is the detailed content of How Can I Automate Email Sending from SQL Server Using T-SQL?. For more information, please follow other related articles on the PHP Chinese website!