How to send mail from SQL Server?

/
1 Comments

How to send mail from SQL Server?

Below are the steps to send email from a database in Microsoft Sql Server:

Step 1: Get the database default mail profile using the below query, if you don't have a database mail profile create one.

DECLARE @defaultProfileName NVARCHAR(100)=''
SELECT TOP 1 @defaultProfileName=name
FROM msdb.dbo.sysmail_profile p
JOIN msdb.dbo.sysmail_principalprofile pp ON pp.profile_id = p.profile_id AND pp.is_default = 1

Step 2: Use the below query to send email with the appropriate arguments such as toEmailAddress, ccEmailAddress, bccEmailAddress, subject, mailBody etc.

DECLARE @toEmailAddress NVARCHAR(100), @ccEmailAddress NVARCHAR(100), @bccEmailAddress NVARCHAR(100), @subject NVARCHAR(200), @mailBody NVARCHAR(MAX),@filePath NVARCHAR(MAX)
SET @toEmailAddress = 'abc@gmail.com'
SET @ccEmailAddress = 'def@gmail.com'
SET @bccEmailAddress = 'ghi@gmail.com'
SET @subject = 'test subject'
SET @mailBody = 'body of the email'
SET @filePath = 'C:\Test\abc.pdf'
IF(ISNULL(@defaultProfileName,'')<>'')
BEGIN
     EXEC msdb.dbo.sp_send_dbmail @profile_name=@defaultProfileName,
    @recipients=@toEmailAddress,
    @copy_recipients=@ccEmailAddress,
    @blind_copy_recipients=@bccEmailAddress,
    @subject=@subject,
    @body=@mailBody,
    @body_format = 'HTML',
    @file_attachments=@filePath
END



About Post Author

Microsoft Certified SQL Server Developer

You may also like

1 comment:

  1. UnknownAugust 7, 2020 at 7:08 PM

    nice and informative content for sending mail from database.

    ReplyDelete

If you have any queries regarding the article or in performing something in Microsoft SQL Server then please let me know.

Followers

Popular Posts