Most of the times there are requirements when we need to send automatic notification emails to all or specific email address in the database based on some condition.

In such scenario we can take help from SQL Server using its following two properties.

1.     SQL Server Emailing

2.     SQL Server Scheduling

 

For example send birthday wishes to all customers whose birthday matches the current day.

Hence I decided I’ll explain the same here.

For this tutorial I have created a database Customers with a table called CustomerDetails with the following fields

1. ID

2. Name

3. BirthDate

4. Email

 

The sample data is shown in the figure below

 


Customers details table

In order to send emails using SQL Server you can refer my article Send SMTP Email using SQL Server

In this tutorial I am using the same stored procedure to send emails.

 

I have written the SQL script shown below. The SQL script loops through the CustomerDetails table and matches all records birth day and birth month with the current day and month. If the two matches then it sends an email to the particular customer to its email address stored in the table.

           

DECLARE

@out_desc VARCHAR(1000),

@out_mesg VARCHAR(10)

 

DECLARE @name VARCHAR(20),

@birthdate datetime,

@email NVARCHAR(50)

 

DECLARE @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 />Customer Relationship Department'

            EXEC sp_send_mail

            sender@abc.com',

            'xxxxxxx',

            @email,

            'Birthday Wishes',

            @body,

            'htmlbody',

            @output_mesg = @out_mesg output,

            @output_desc = @out_desc output

 

            PRINT @out_mesg

            PRINT @out_desc

      END

      FETCH NEXT FROM C1 INTO

      @name, @birthdate, @email

END

CLOSE C1

DEALLOCATE C1

 

 

Now to make the above script automatically run daily we will need to schedule it to run daily using the SQL Server Job Scheduler.

Below I’ll explain how to schedule the script to run daily using Job Scheduler

  

 

Step 1

In the SQL Server Enterprise Manager expand the Management Tab and select SQL Server Agent Node.

Refer figure below.



Management Tab

Step 2

On the Right Panel Select Job, Right Click it and select New Job from context menu to open New Job Window.

Refer figure below.



Create a New Job

Step 3

In the New Job Window, in General Tab enter the following details

1.     Name - Name of the Job

2.     Description – Description of the Job (Optional)

3.     Enabled – Determines whether job is enabled or disabled

Refer figure below



New Job - General Tab

Step 4

In the New Job Window, in Steps Tab click New Step to open a New Step Window

In the New Step Window enter the following details

1.     Step Name - Name of the Step

2.     Type  – Select Transact SQL Script

3.     Database – Select the database on which you want to run the script.

4.     Command – Paste the SQL Script which you wish the Job Scheduler to run.

 

Refer figure below



New Job - Steps Tab

Step 5

In the New Job Window, in Schedules Tab click New Schedule to open a New Schedule Window

In the New Schedule Window enter the following details

1.     Name - Name of the Step

2.     Enabled – Determines whether Schedule is enabled or disabled

3.     Schedule Type – Select Recurring schedule type since we need to run it daily

 

Refer figure below



New Job - Schedule Tab - New Schedule

Step 6

Next click on Change button in the Schedule Window to set the schedule

In the Edit Schedule Window enter the following details

1.     Occurs – Daily since we need to run it daily

2.     Daily Frequency – Since we need to run once a day, select the time you wish to run

3.     Start date – Select date from when you want the schedule to run.

 

Refer figure below



New Job - Schedule Tab - Edit Recurring Schedule

Step 7

That’s it and your job is created. You will see a new entry in the SQL Server agent -----> Jobs

To start the job right click the job and in the context menu click Start Job

Refer figure below



Start Scheduled Job

This completes the tutorial for creating scheduled job in SQL Server. Below is the Birthday email that will be received by the customer

Refer figure below



Received Birthday Email Notification

 

This completes the tutorial for creating scheduled job in SQL Server. You can download the sample database and scripts here.


Download Code (72.10 kb)


Please feel free to post comments for any queries.