SQL Server Database Mail sp_send_dbmail Example
Here is a sample t-sql code using sp_send_dbmail system stored procedure to send email from SQL Server.
Since Database Mail feature is first introduced to administartors and developers with MS SQL Server 2005, SQL Server administrators and t-sql developers can use the below sp_send_dbmail example sql code on a Microsoft SQL Server 2005, MS SQL Server 2008 or SQL Server 2008 R2 instance.
sp_send_dbmail Profile
Note that before successfully using Database Mail on SQL Server, database administrators should enable and configure Database Mail on that SQL Server instance.
For a SQL Server tutorial on enabling and configuring Database Mail, please refer to guide How to configure SQL 2005 Database Mail sp_send_dbmail.
The below sql Database Mail code, assumes that sp_send_dbmail profile with a name "General Administration Profile" has already created before calling the sp_send_dbmail command.
You can change the sp_send_dbmail profile name with the Database Mail profile you have created.
DECLARE @p_body as nvarchar(max), @p_subject as nvarchar(max)
DECLARE @p_recipients as nvarchar(max), @p_profile_name as nvarchar(max)
SET @p_profile_name = N'General Administration Profile'
SET @p_recipients = N'recipient@testemail.com;multiple.recipients@dbmail.com'
SET @p_subject = N'This is a test mail using sp_send_dbmail'
SET @p_body = 'This is an HTML test email send using <b>sp_send_dbmail</b>.'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @p_profile_name,
@recipients = @p_recipients,
@body = @p_body,
@body_format = 'HTML',
@subject = @p_subject
sp_send_dbmail HTML
As you will realize in the sp_send_dbmail t-sql code, by setting the @body_format parameter to "HTML", sql developers and DBA's can send sp_send_dbmail HTML emails easily from their SQL Server applications.
sp_send_dbmail Multiple Recipients
It is also possible to send emails to multiple recipients using sp_send_dbmail.
In order to send mail using sp_send_dbmail multiple recipients, @p_recipients parameter should have concatenated emails with ";" semi-colon.
So in your Database Mail sending procedure, if you concatenate emails using ";" you can send emails to multiple recipients using sp_send_dbmail system procedure.
For SQL developers who want to send email with file attachments from SQL Server please refer to sql codes in tutorial at SQL Server Email using sp_send_dbmail with File Attachment
SQL developers who have worked both on SQL Server 2005 Database Mail and SQL Server 2008 Database Mail will realize that Database Mail setup in SQL Server 2008 R2 has a few more controls on profiles and accounts when compared.
I will soon publish a tutorial with screenshots on MS SQL Server 2008 Database Mail setup and point to differences from database mail in SQL Server 2005.
It is obvious that using database mail is more convenient and easier than using sp_OACreate to send emails from SQL Server database applications.