Run a Job on few specific dates of every month in SQL Server

Introduction

As per business requirements, a report needs to generate four times every month. The report will generated by a Stored Procedure. So the DBA’s task is to execute the specific Stored Procedure on a few specific dates of every month by an automated process. The simplest process is to create a job with four different schedules within a job for a single task.

For demonstration, we will use a backup script in place of the mentioned Stored Procedure to generate the report and run the same every month on specific four dates of every month.

Creating Job by SSMS

First of all, we will do the activity by SSMS. Connect to the SQL Server using SSMS. Right-click on SQL Server Agent > New > Job

In General section Name: [Name of the Job], Owner: [sa is recomentaded], Category: as per you.

In Steps section, click the New button at the left down corner of the window.

Here we need to fill Step name, select Type, Select Database name & Paste Command

Here we have use backup script in command

In Schedules section, click the New button at the left down corner of the window.

First schedule for the task. Provide Name (any name), Schedule type (Recurring), Frequency – Occurs (Monthly), Day (Date as per requirement), Daily frequency (Time for execution). So we have provided 1st date of the month & time at 9 am.

Second schedule for the task. Provide Name (any name), Schedule type (Recurring), Frequency – Occurs (Monthly), Day (Date as per requirement), Daily frequency (Time for execution). So we have provided 8th date of the month & time at 9 am.

Third schedule for the task. Provide Name (any name), Schedule type (Recurring), Frequency – Occurs (Monthly), Day (Date as per requirement), Daily frequency (Time for execution). So we have provided 15th date of the month & time at 9 am.

Fourth schedule for the task. Provide Name (any name), Schedule type (Recurring), Frequency – Occurs (Monthly), Day (Date as per requirement), Daily frequency (Time for execution). So we have provided 25th date of the month & time at 9 am.

All four schedules created

Single Job with a single task with four different dates schedule is created

Creating Job by TSQL


Comments

Leave a Reply

Your email address will not be published. Required fields are marked *