Creating the SQL Server Agent Job for Student Export

A SQL Server Agent script that creates a scheduled job to export student data to Navigate every night at midnight. The job runs the update and insert stored procedures in sequence.

What the job does

The script creates a SQL Server Agent job named Navigate Student Export with two steps, attaches a daily midnight schedule, and assigns the job to the SQL Server Agent. Step one runs the update procedure; step two runs the insert procedure. You can amend the job name, schedule name and run time to suit your environment.

Create the job and steps

1. Run the script against the msdb database.
2. sp_add_job creates the job (enabled, with a description).
3. sp_add_jobstep adds the Update Navigate step, running EXEC ProSolutionReports.dbo.Navigate_StudentUpdate; against the ProSolutionReports database. On success it moves to the next step; on failure it quits with failure.
4. sp_add_jobstep adds the Insert Navigate step, running EXEC ProSolutionReports.dbo.Navigate_StudentInsert;. On success it quits with success; on failure it quits with failure.

Schedule and assign the job

1. sp_add_schedule creates the DailyMidnight schedule (daily, every day, starting at 00:00:00). Change the start time as needed, using 24-hour format.
2. sp_attach_schedule attaches the schedule to the job.
3. sp_add_jobserver assigns the job to the SQL Server Agent on the current server.

The script

USE msdb;
GO

-- Creates the job
EXEC sp_add_job
    @job_name = 'Navigate Student Export', --Amend to custom name if required
    @enabled = 1,
    @description = 'This job runs a stored procedure every night at Midnight.';
GO

-- Add a job step for update
EXEC sp_add_jobstep
    @job_name = 'Navigate Student Export',
    @step_name = 'Update Navigate',
    @subsystem = 'TSQL',
    @command = 'EXEC ProSolutionReports.dbo.Navigate_StudentUpdate;',
    @database_name = 'ProSolutionReports',
    @on_success_action = 1,  -- Go to next step
    @on_fail_action = 2;  -- Quit with failure
GO

-- Add a job step for insert
EXEC sp_add_jobstep
    @job_name = 'Navigate Student Export',
    @step_name = 'Insert Navigate',
    @subsystem = 'TSQL',
    @command = 'EXEC ProSolutionReports.dbo.Navigate_StudentInsert;',
    @database_name = 'ProSolutionReports',
    @on_success_action = 3,  -- Quit with success
    @on_fail_action = 2;  -- Quit with failure
GO

-- Schedule the job to run daily at midnight
EXEC sp_add_schedule
    @schedule_name = 'DailyMidnight',
    @freq_type = 4,  -- Daily
    @freq_interval = 1,  -- Every day
    @active_start_time = 000000;  -- Midnight (00:00:00) (change this to required time, must be in 24 hour format)
GO

-- Attach the schedule to the job
EXEC sp_attach_schedule
    @job_name = 'Navigate Student Export',
    @schedule_name = 'DailyMidnight'; --change to what you'd like
GO

-- Assign the job to SQL Server Agent
EXEC sp_add_jobserver
    @job_name = 'Navigate Student Export',
    @server_name = @@SERVERNAME;
GO

On success and on fail action codes

• 1 = Go to next step.
• 2 = Quit the job with failure.
• 3 = Quit the job with success.

Source document attached below.