In this article I will explain with an example, how to implement automated email notification using
SQL Server Job Scheduler.
This article will illustrate, how to send automatic email notification to email address from the database based on some condition using
SQL Server Job Schedular.
This article makes use of
SQL Server 2022. It is also applicable to
SQL Server 2016, 2017 and 2019 versions.
Database
I have made use of the following table Customers with the schema as follows.
![Automated Email Notifications using SQL Server Job Schedular]()
I have already inserted few records in the table.
![Automated Email Notifications using SQL Server Job Schedular]()
Note: You can download the database table SQL by clicking the download link below.
Script for sending email using SQL Server Job Schedular
Following script will be used to send email using
SQL Server Job Schedular.
Inside the script, a WHILE loop will be executed over the records of Customers table using Cursor.
Inside the WHILE loop, each record’s birth day and birth month is matched with current day and month.
If matching record is found, then an email is sent to the particular customer’s email address stored in the table using the
msdb.dbo.sp_send_dbmail Stored Procedure.
DECLARE @name VARCHAR(20),
@birthdate DATETIME,
@email NVARCHAR(50),
@body NVARCHAR(1000)
DECLARE C1 CURSOR READ_ONLY
FOR
SELECT [Name], [BirthDate], [Email]
FROM [Customers]
OPEN C1
FETCH NEXT FROM C1 INTO
@name, @birthdate, @email
WHILE @@FETCH_STATUS= 0
BEGIN
IF DATEPART(DAY, @birthdate)=DATEPART(DAY,GETDATE())
AND DATEPART(MONTH, @birthdate)=DATEPART(MONTH,GETDATE())
BEGIN
SET @body ='<b>Happy Birthday '+ @name +'</b>.<br />'
+'Many happy returns of the day.'
+'<br /><br />ASPSnippets Team'
EXEC msdb.dbo.sp_send_dbmail
@profile_name ='Mudassar_Email_Profile'
,@recipients = @email
,@subject ='Birthday Wishes'
,@body = @body
,@body_format ='HTML'
,@importance ='HIGH'
END
FETCH NEXT FROM C1 INTO
@name, @birthdate, @email
END
CLOSE C1
DEALLOCATE C1
Steps for creating Job and Scheduling in SQL Server
In order to create Job Schedular, please follow the below steps.
First, open
SQL Server Management Studio, connect to the instance of the
SQL Server Database Engine and expand that instance.
Next, expand the SQL Server Agent and right click on Jobs Folder and select New Job.
![Automated Email Notifications using SQL Server Job Schedular]()
Inside the New Job dialog window, specify the name of the job in the Name TextBox.
![Automated Email Notifications using SQL Server Job Schedular]()
Then, select the Steps tab from left panel and click on the New button.
![Automated Email Notifications using SQL Server Job Schedular]()
Inside the New Job Step dialog window, set the following details:
Step name – Specify the name for the step.
Type – Select Transact SQL Script type. Here it is specified as Transact-SQL script (T-SQL).
Database – Select the database on which the script will be run.
Command – The SQL Script which will be run on Job Schedular.
After filling the details, click on OK Button.
![Automated Email Notifications using SQL Server Job Schedular]()
Next, select the Schedules tab in the left panel and click on the New button.
![Automated Email Notifications using SQL Server Job Schedular]()
Inside the New Job Schedule dialog window, set the following details:
Name – Name of the schedule.
Schedule type – Select schedule type. Set as Recurring since it will run on daily basis.
Frequency:
Occurs – Here it is specified as Daily.
Recurs every – Specify the number of days. Here it is specified as 1, so that it will be run every day.
Daily Frequency – Select the frequency RadioButton i.e. Occurs once at or Occurs every.
If Occurs once at is selected, then it will be executed on the specified time.
If Occurs every is selected, then regular intervals between a specified period specified using Starting at and Ending at time.
Duration – It specifies the duration of job to be executed. In this case it is specified as No end date.
After filling the details, click on OK button.
![Automated Email Notifications using SQL Server Job Schedular]()
Finally, click on OK button. Once the Job is added, right click on the Job you added and select Start Job at Step.
![Automated Email Notifications using SQL Server Job Schedular]()
Once the job is scheduled and started successfully, you will see the success dialog message.
![Automated Email Notifications using SQL Server Job Schedular]()
Downloads
