How to send email from SQL Server?
Here's an example of how you might concatenate email addresses from a table into a single @recipients parameter:
CREATE TABLE #emailAddresses (email VARCHAR(25))
INSERT #emailAddresses (email) VALUES ('[email protected]')
INSERT #emailAddresses (email) VALUES ('[email protected]')
INSERT #emailAddresses (email) VALUES ('[email protected]')
DECLARE @recipients VARCHAR(MAX)
SELECT @recipients = COALESCE(@recipients + ';', '') + email
FROM #emailAddresses
SELECT @recipients
DROP TABLE #emailAddresses
The resulting @recipients will be:
[email protected];[email protected];[email protected]
You can send email natively from within SQL Server using Database Mail. This is a great tool for notifying sysadmins about errors or other database events. You could also use it to send a report or an email message to an end user. The basic syntax for this is:
EXEC msdb.dbo.sp_send_dbmail
@recipients='[email protected]',
@subject='Testing Email from SQL Server',
@body='<p>It Worked!</p><p>Email sent successfully</p>',
@body_format='HTML',
@from_address='Sender Name <[email protected]>',
@reply_to='[email protected]'
Before use, Database Mail must be enabled using the Database Mail Configuration Wizard, or sp_configure. A database or Exchange admin might need to help you configure this. See http://msdn.microsoft.com/en-us/library/ms190307.aspx and http://www.codeproject.com/Articles/485124/Configuring-Database-Mail-in-SQL-Server for more information.
Step 1) Create Profile and Account
You need to create a profile and account using the Configure Database Mail Wizard which can be accessed from the Configure Database Mail context menu of the Database Mail node in Management Node. This wizard is used to manage accounts, profiles, and Database Mail global settings.
Step 2)
RUN:
sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO
Step 3)
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.'
To loop through the table
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
Posted this on the following link http://ms-sql-queries.blogspot.in/2012/12/how-to-send-email-from-sql-server.html
You can do it with a cursor also. Assuming that you have created an Account and a Profile e.g. "profile" and an Account and you have the table that holds the emails ready e.g. "EmailMessageTable" you can do the following:
USE database_name
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE mass_email AS
declare @email nvarchar (50)
declare @body nvarchar (255)
declare test_cur cursor for
SELECT email from [dbo].[EmailMessageTable]
open test_cur
fetch next from test_cur into
@email
while @@fetch_status = 0
begin
set @body = (SELECT body from [dbo].[EmailMessageTable] where email = @email)
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'profile',
@recipients = @email,
@body = @body,
@subject = 'Credentials for Web';
fetch next from test_cur into
@email
end
close test_cur
deallocate test_cur
After that all you have to do is execute the Stored Procedure
EXECUTE mass_email
GO