SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2017
download SQL Server 2016
download SQL Server 2014



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.






Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums









Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems