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