Send emails from SQL Server


To use this feature from SQL Server, you can use the stored procedure sp_send_dbmail following the next steps:

Configure the Database Mail

Open, within the SQL Server Management Studio, the Database Mail item.

Do it with double click on it or with right click and selecting Configure Database Mail.

rightClick

It will start the wizard to configure the Database Mail.

Skip the first screen, and click on Next on the second screen to set up the service.

screen1

Click Yes to enable the feature:

confirmDialog

Write a name and a description for the new profile and click on Add to add a new SMTP account:

AddProfile

Fill all the needed fields and click Ok:

accountData

Click on Next to go to the next screen.

Check the checkbox on the column Public to make the profile public:

publicProfile

Click on Next to go to the next screen.

If you need it, change values like Retry Attempts, etc, on this screen, if not, simply click on Next to go to the next screen.

Parameters

Click on Finish and wait to end the feature activation and click on Close.

Now open a New Query window. We are going to set the Profile as default with this script:


EXECUTE msdb.dbo.sysmail_update_principalprofile_sp
 @profile_name = 'Test Profile',
 @principal_name = 'public',
 @is_default = 1 ;

Now we can write a, for instance, a trigger which sends an email in certain circumstancies. Let’s say we have a table called Names and we want to know when a record is updated. We can write a trigger like this one:

CREATE TRIGGER TESTEMAIL
 ON NAMES
 AFTER UPDATE
AS
BEGIN
 EXEC msdb.dbo.sp_send_dbmail
 @profile_name=N'Test_Email',
 @importance=N'High',
 @recipients=N'receiver_email@server.com',
 @subject=N'Testing email from db',
 @body=N'Names record updated.' ;

END
GO

That’s it. I hope it will help you.

More documentation about this Stored Procedure on:
http://msdn.microsoft.com/en-us/library/ms190307.aspx
http://technet.microsoft.com/en-us/library/ms175036(v=sql.105).aspx
http://msdn.microsoft.com/en-us/library/ms187891.aspx

Visit my profile on Google Plus

Anuncios