Submitted by Nizam on

Step1: First of all you need to enable and setup Data mail profile and account. Below is the script just copy and paste to your SQL Server query window and change the parameters accordingly.

 /*Run this script after setting proper configurations*/ 

-- ENABLE DATABASE MAIL FEATURE IN SYSTEM CONFIGURATION

use master

go

sp_configure 'show advanced options',1

go

reconfigure with override

go

sp_configure 'Database Mail XPs',1

go

reconfigure

go 

-- DECLARE DATABASE MAIL ACCOUNT PARAMETERS

Declare @qmail_account_name as varchar(64);

Declare @qmail_account_description as varchar(64);

Declare @qmail_account_email_address as varchar(64);

Declare @qmail_account_display_name as varchar(64);

Declare @qmail_account_username as varchar(64);

Declare @qmail_account_password as varchar(64);

Declare @qmail_account_mailserver_name as varchar(64);

 

-- DECLARE PROFILE PARAMETERS

declare @qmail_profile_profile_name as varchar(64);

declare @qmail_profile_description as varchar(64);

-- DECLARE PRINCIPLES

declare @qprincipal_name as varchar(16);

-- SET SMTP ACCOUNT DETAILS, USERNAME AND PASSWORD HERE

set @qmail_account_mailserver_name = 'smtp.bizmail.yahoo.com' -- your smtp address

set @qmail_account_username='something@something.com'  -- username

set @qmail_account_email_address = 'myEmailName@something.com' -- email address

set @qmail_account_password='password for user (something@something.com)'  -- password for username

set @qmail_account_name = 'YourAccountName (e.g MyAppName_EmailAccount)'

set @qmail_account_description = 'Mail account for administrative e-mail.'

set @qmail_account_display_name = 'MyApplicationName Automated Mailer'

-- PROFILE PARAMETERS ASSIGNMENT

set  @qmail_profile_profile_name = 'MyAppNameEmailServiceProfile'

set  @qmail_profile_description = 'Profile used for database mail jobs'

 

--PROFILE TYPE

set @qprincipal_name= 'public'

if (      @qmail_account_mailserver_name = '' or

@qmail_account_username=''  or 

@qmail_account_email_address = ''  or @qmail_account_password='' )

begin

   Select 'Please enter SMTP details' as [Comments]

end

else

Begin

                  if exists(select * from  msdb.dbo.sysmail_account where[name]=@qmail_account_name)

                  begin

                  exec msdb.dbo.sysmail_delete_account_sp @account_name =@qmail_account_name

                  end

 

                  EXECUTE msdb.dbo.sysmail_add_account_sp

                    @account_name = @qmail_account_name,

                        @description = @qmail_account_description,

                        @email_address = @qmail_account_email_address,

                        @display_name = @qmail_account_display_name,

                        @username=@qmail_account_username,

                        @password=@qmail_account_password,

                        @mailserver_name = @qmail_account_mailserver_name

 

                  if exists(select * from  msdb.dbo.sysmail_profile where[name]=@qmail_profile_profile_name)

                  begin

                  exec msdb.dbo.sysmail_delete_profile_sp @profile_name =@qmail_profile_profile_name

                  end

 

                  EXECUTE msdb.dbo.sysmail_add_profile_sp

                           @profile_name = @qmail_profile_profile_name,

                           @description = @qmail_profile_description

 

                  EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

                        @profile_name = @qmail_profile_profile_name,

                        @account_name = @qmail_account_name,

                        @sequence_number = 1

 

                  EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

                        @profile_name = @qmail_profile_profile_name,

                        @principal_name = @qprincipal_name,

                        @is_default = 1 ;

end

 

Step2: Create a Sproc which will send email notification. Below is the script for that only you need to change your email To/CC, Subject, Body from your application tables or you can set anything you want. Please make sure Email profile name is proper it should be same as  you have configured during Database Email Setup.

 if exists (select 1 from sys.objects where name = 'p_SendEmailNotification' andtype = 'p')

drop procedure p_SendEmailNotification

go

 

Create Procedure [dbo].[p_SendEmailNotification]

as

Begin

Declare @ErrorCode as int 

BEGIN TRANSACTION 

declare @MailPriority varchar(8);

declare @sendAddressTo varchar (256);

declare @sendAddressCC varchar(256);

declare @MailBodyFormat varchar(8);

declare @DBMailProfile varchar(32);

 

set @MailBodyFormat ='HTML'

set @DBMailProfile = 'Email Profile Name which you have created during DB Mail setup (e.g MyAppNameEmailServiceProfile)'

set @MailPriority = 'Normal'

 

select @sendAddressTo = 'someone@someone.com,abc@abc.com'

 

select @sendAddressCC ='someone@someone.com,xyz@xyz.com'

 

EXEC msdb.dbo.sp_send_dbmail

                        @profile_name = @DBMailProfile,

                        @recipients=@sendAddressTo,

                        @copy_recipients  = @sendAddressCC,

                        @importance = @MailPriority,

                        @subject = 'Email Subject',

                        @body = 'Email Body....hi this is test email',

                        @body_format = @MailBodyFormat ;

                       

 set @ErrorCode = @@ERROR 

 if(@ErrorCode <> 0) 

 begin  

  goto ERR_HANDLER  

 end

 

 COMMIT TRANSACTION   

Set @ErrorCode = @@Error 

if @ErrorCode<>begin goto ERR_HANDLER end 

 

ERR_HANDLER: 

if @ErrorCode<>

Begin 

 ROLLBACK TRANSACTION 

End

End

You are done, now if you just need to call this sproc and this will send the email, if you want to Automate this email as some alert then better you create a job and within that job call this sproc as per your needed schedule.

 

Tags: