T-SQL Tutorial

Scheduled stored procedure in SQL Server

Scheduling SQL Server stored procedures is a common task in database administration to automate routine tasks, reports generation, and data maintenance. SQL Server provides various ways to schedule the execution of stored procedures, and one of the most commonly used methods is using SQL Server Agent and T-SQL. Here's a step-by-step guide on how to schedule a SQL Server stored procedure using T-SQL:

Create the Stored Procedure: First, you need to have the stored procedure that you want to schedule. If you don't have one, you can create it using SQL Server Management Studio (SSMS) or any other SQL Server management tool.

CREATE PROCEDURE dbo.MyScheduledProcedure
-- Your SQL code here

Open SQL Server Management Studio (SSMS): Launch SSMS and connect to your SQL Server instance where you want to schedule the stored procedure.
Open SQL Server Agent: In SSMS, expand the "SQL Server Agent" node in the Object Explorer.
Create a New Job: Right-click on "Jobs" and select "New Job..." to create a new job. Give your job a meaningful name and description.
Add a Job Step: In the job properties window, click on the "Steps" tab and then click "New..." to add a new job step. A job step represents the action to be performed when the job is executed.
Configure the Job Step:
Step Name: Give the step a name.
Type: Choose "Transact-SQL script (T-SQL)" as the step type.
Database: Specify the database where your stored procedure exists.
Command: Enter the T-SQL code to execute your stored procedure. For example:

EXEC dbo.MyScheduledProcedure

Schedule the Job: Switch to the "Schedules" tab in the job properties window and click "New..." to create a schedule for your job. Define the frequency, start date, and time when you want the job to run.
Set Notifications (Optional): You can configure email notifications for job success or failure by going to the "Notifications" tab in the job properties window.
Save and Enable the Job: Once you've configured the job, click "OK" to save it. Right-click on the job in the SQL Server Agent and select "Start Job at Step" to manually run the job and verify that it executes the stored procedure correctly.
Enable the Job: To enable the job to run automatically according to the schedule you defined, right-click on the job and select "Enable."

Now, your SQL Server stored procedure is scheduled to run at the specified intervals automatically. SQL Server Agent will take care of executing the job based on your schedule, and you can monitor its execution and view the job history in SSMS.

Permissions: Ensure you have the necessary permissions to create and manage SQL Server Agent jobs.
SQL Server Agent: This service must be running to execute scheduled jobs.
Error Handling: Consider adding error handling in your stored procedure for better diagnostics in case of failures.
Logging: Implement logging within your stored procedure or job to track executions and outcomes.

Remember to ensure that the SQL Server Agent service is running and that the necessary permissions are granted to the user running the job to execute the stored procedure successfully.