Automated email notification using SQL Server Job Scheduler

Introduction: Hello Guys, “Welcome back” Today, i am here with another one new great article. In this article I explained about how to implement Automated email notification using SQLServer Job Scheduler.

This article will also cover the details, how to send automatic email notification to email address from the database based on some condition using SQL Server Job Scheduler.

This article is compatible for SQL Server edition 2022. It is also compatible to SQL Server 2016, 2017 and 2019 versions or editions.

 Database Details

I created the following table named userdetail with the all details as follows.

 Column name         data Type

 userid                        int

 username                 varchar(100)

 useremail                 varchar(50)

useraddress             varchar(250)

userdob                    datetime

I have already inserted few records in the table.

userid       username                 useremail                        useraddress                                         useraddress

1                   Sanjeev                  sanjeev@gmail.com    #250 Sector 127, Chandigarh          1988-02-10

2                   Sanjay                    sanjay@gmail.com       #123 Sector 255, Chandigarh          1980-12-30

3                   Ruhika                    ruhika@gmail.com       #256 Sector 855, Chandigarh          1990-05-25

4                   Rajan                      rajan@gmail.com         #55 Sector 00, Chandigarh               1995-08-22

 

Here I am also giving SQL Server Database Script, you can execute this SQL Database Script by using copy and paste to create table with column and data.

         SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

SET ANSI_PADDING ON

GO

 

CREATE TABLE [dbo].[userdetails](

            [userid] [int] IDENTITY(1,1) NOT NULL,

            [username] [varchar](100) NOT NULL,

            [useremail] [varchar](50) NOT NULL,

            [useraddress] [varchar](250) NOT NULL,

          [userdob] [datetime] NOT NULL,

 CONSTRAINT [PK_userdetails] PRIMARY KEY CLUSTERED

(

            [userid] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

 

SET ANSI_PADDING OFF

GO

SQL Script for sending email using SQL Server Job Scheduler

This SQL script needs to use to send email using SQL Server Job Scheduler. In this script I used while loop. For understanding about the detailed understanding on the While loop, please refer my article.  While loop will be work or execute on the records available in the used table named userdetiails by using Cursor. For detailed information about Cursor, Check my article on Cursor. In this SQL script I used the WHILE loop, to compare the birth day and birth month each record available in the table with current day and month one by one. If any record will be match with each other means if any record available in the table will be matched with the current day and month then email will be sent to the particular email of the user that is stored in the table using the msdb.dbo.sp_send_dbmail Store procedure. For more details on configuring and sending email in SQL Server, please check my article Send Email in SQL Server using Store Procedure.   

DECLARE @username VARCHAR(20),

    @userdob DATETIME,

    @useremail NVARCHAR(50),

    @body NVARCHAR(1000)

 

DECLARE Send_email_Cur CURSOR READ_ONLY

FOR

SELECT [username], [userdob], [useremail]

FROM [userdetails]

 

OPEN C1

FETCH NEXT FROM C1 INTO

@username, @userdob, @useremail

WHILE @@FETCH_STATUS= 0

BEGIN

      IF DATEPART(DAY, @ userdob)=DATEPART(DAY,GETDATE())

         AND DATEPART(MONTH, @userdob)=DATEPART(MONTH,GETDATE())

      BEGIN

            SET @body ='<b>Happy Birthday '+ @username +'</b>.<br />'

                      +'Many Many Congratulation for the day.'

                      +'<br /><br />Using Asp.Net'

            EXEC msdb.dbo.sp_send_dbmail

                  @profile_name ='SanjeevKumar'

               ,@recipients = @useremail

               ,@subject ='Birthday Wishes'

               ,@body = @body

               ,@body_format ='HTML'

              ,@importance ='HIGH'

      END

      FETCH NEXT FROM C1 INTO

      @username, @userdob, @useremail

END

CLOSE Send_email_Cur

DEALLOCATE Send_email_Cur

 

Steps for creating Job and Scheduling in SQL Server

For more details on create job and scheduling in SQL Server, Please refer this Article.

For creating Job Scheduling, follow the below steps.

Firstly open the SQL Server Management Studio and connect to the instance of the SQL Server Database Engine and expand that instance. Then next, expand the SQL Server Agent and right click on folder named Jobs and select the option named New Job. When you will be click on the New Job option then  dialogbox will be appear on the screen that will be specify the name of the job in the Name TextBox. After that please click on the tab named steps from left panel and then click on the button named New. Then inside the New Job Step dialogbox will be appear on the screen, set the following details:

Step name – Give the name for the step.

Type – Select Transact SQL Script type. Here it is specified as Transact-SQL script (T-SQL).

Database – Select the name of the database on which the SQL script will be execute.

Command – The SQL Script which will be run on Job Scheduler.

After filling the above details, click on OK Button. Next, click on the Schedules tab in the left panel and click on the new button. A new dialogbox will be appearing on the screen with New Job Schedule, set the following details in this dialogbox :

Name – Name of the schedule as per your requirement.

Schedule type – Select schedule type. Set schedule type as Recurring since it will run on daily basis.

Frequency:

Occurs – it is specified as daily basis.

Recurs every – Specify the number of days. Here it is specified as 1, so that it will be run every day.

Daily Frequency – Select anyone of the frequency from the RadioButton i.e. Occurs once at or Occurs every.

If you will select Occurs once at, then it will be executed on the specified time that you will provide it.

If you will select Occurs every, 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 complete details, click on OK button. Finally, click on OK button. Once the Job is added, right click on the Job you added and select Start Job at Step. Once the job is scheduled and started successfully.

Conclusion: In above code, I have been explained implementation about explained the implementation about how to implement automated email notification using SQL Server Job Scheduler. This code is very helpful for every developer. Bye and take care of yourself Developers. We will come back shortly with the new article.

Regards

Using Asp.net