How to send email from SQL Server?

Question

How can I send an email using T-SQL but email address is stored in a table? I want to loop through the table and be able to send email. I cannot find a good example of doing this so far.

Solution 1

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

Solution 2

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.

Solution 3

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

Solution 4

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]

Solution 5

In-order to make SQL server send email notification you need to create mail profile from Management, database mail.

1) User Right click to get the mail profile menu and choose configure database mail

2)choose the first open (set up a database mail by following the following tasks) and press next Note: if the SMTP is not configured please refer the the URL below

http://www.symantec.com/business/support/index?page=content&id=TECH86263

3) in the second screen fill the the profile name and add SMTP account, then press next

4) choose the type of mail account ( public or private ) then press next

5) change the parameters that related to the sending mail options, and press next 6) press finish

Now to make SQL server send an email if action X happened you can do that via trigger or job ( This is the common ways not the only ones).

1) you can create Job from SQL server agent, then right click on operators and check mails (fill the your email for example) and press OK after that right click Jobs and choose new job and fill the required info as well as the from steps, name, ...etc and from notification tab select the profile you made.

2) from triggers please refer to the example below.

AS
declare @results varchar(max)
declare @subjectText varchar(max)
declare @databaseName VARCHAR(255)
SET @subjectText = 'your subject'
SET @results = 'your results'
-- write the Trigger JOB
EXEC msdb.dbo.sp_send_dbmail
 @profile_name = 'SQLAlerts',
 @recipients = '[email protected]',
 @body = @results,
 @subject = @subjectText,
 @exclude_query_output = 1 --Suppress 'Mail Queued' message
GO

View more solutions

Comments

[moe]

Thanks Ruzbeh, but i need to get the list of emails from the my table. how can i loop thru the table and get each email from the table and send the email? the method that you showed only uses one fixed email. i look forward hearing from you soon. thanks

[Ruzbeh Irani]

i have edited the answer.. check it..here id is the identity column in your address table

[Jimmy]

Should @max_id=MIN(id) be @max_id=MAX(id)? Otherwise it seems no emails will be sent.

[jitendragarg]

To add on to the this excellent answer, email can be sent only from MSDB by default. Took me few minutes to figure out why my sp was failing when I used this method.

[TT.]

Note that jon.o'[email protected] is a valid email address according to RFC 5322. The +@email_id+ will break in that case.

[MarredCheese]

Shouldn't SELECT @email_id=email_id FROM [email_adresses] be 'SELECT @email_id=email_id FROM [email_adresses] WHERE id = @id? Otherwise, @email_id isn't connected to @id at all, if I'm not mistaken.

[Matt]

Good Answer EXCEPT the dynamic SQL. There is no reason for dynamic SQL and it opens up more problems such as TTs comment about email addresses wtih single quotes (') in them. The preferred method would be to call the stored procedure directly and pass @email_id as the parameter. And the while loop should work but cursor will take less management to determine which emails have and have not been sent.

[BilliD]

Since no ones mentioned it I will, DB mail's intention is to send status updates and such to DBA's and not for mass mail distribution. Yes it works in small cases and I have used it to send reports and updates to small teams but looping through a table and sending an email to each could stress your server.

[TylerH]

This doesn't answer the question at all.