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'
and
type
=
'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<>0 begin
goto ERR_HANDLER end
ERR_HANDLER:
if @ErrorCode<>0
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.
