SQL Server Agent job

Job

Add
EXEC msdb.dbo.sp_add_job
	@job_name = N'JobForDepartment',
	@enabled = 1,
	@description = N'Execute Procedure GetDepartment everyDay';
GO
View
EXEC msdb.dbo.sp_help_job;

Create Job

Update
EXEC msdb.dbo.sp_update_job 
	@job_name = 'JobForDepartment', 
	@enabled = 1;
GO
Delete
EXEC msdb.dbo.sp_delete_job 
	@job_name = 'JobForDepartment';

JobStep

Add
EXEC msdb.dbo.sp_add_jobstep
	@job_name = N'JobForDepartment',
	@step_name = N'StepRunProcedure',
	@subsystem = N'TSQL',
	@command = N'EXEC ForDepartment';
GO
View
EXEC msdb.dbo.sp_help_jobstep 
	@job_name = 'JobForDepartment';

JobStep

Update
EXEC msdb.dbo.sp_update_jobstep 
	@job_name = 'JobForDepartment',
	@step_id = 1,
	@command = 'EXEC ForDepartment2';
GO
Delete
EXEC msdb.dbo.sp_delete_jobstep 
	@step_id = 1, 
	@job_name = 'JobForDepartment';

Schedule

Add
EXEC msdb.dbo.sp_add_schedule
	@schedule_name = N'EveryDay',
	@freq_type = 4,
	@freq_interval = 1,
	@active_start_time = '123000';
GO
View
EXEC msdb.dbo.sp_help_schedule;

Schedule

@freq_type
ValueDescription
1Once
4Daily
8Weekly
16Monthly
32Monthly, relative to freq_interval
64Run when SQLServerAgent Service starts
128Run when the computer is idle

Schedule

@freq_interval
Value of freq_typeEffect on freq_interval
1(Once)freq_interval is unused
4(daily)Every freq_interval days
8(weekly)1 = Sunday, 2 = Monday, 4=Tuesday, 8=Wednesday, 16 = Thursday, 32=Friday, 64=Saturday

Schedule

@freq_interval
16(monthly)On the freq_interval day of the month
32(monthly relative)Freq_interval is one of the following:
1=Sunday, 2=Monday, 3=Tuesday, 4=Wednesday, 5=Thursday, 6=Friday, 7=Saturday, 8=Day, 9=Weekday, 10=Weekend day

Schedule

Update
EXEC msdb.dbo.sp_delete_schedule 
	@schedule_id = 10;
Delete
EXEC msdb.dbo.sp_update_schedule 
	@schedule_id = 10, 
	@enabled = 0;

Attach Schedule to Job

EXEC msdb.dbo.sp_attach_schedule
	@job_name = N'JobForDepartment',
	@schedule_name = N'EveryDay';
GO

Detach Schedule to Job

EXEC msdb.dbo.sp_detach_schedule 
	@job_name = 'JobForDepartment', 
	@schedule_name = 'EveryDay';

Add JobServer

EXEC msdb.dbo.sp_add_jobserver
	@job_name = N'JobForDepartment',
	@server_name = @@SERVERNAME;
GO