Iâve got a script that creates a job, jobstep and schedule in SQL Server. The schedule calls
stored procedures daily. This is working OK and I get the expected results.
However, this script needs to be incorporated into a stored procedure, to be run against the
System Database 'msdb'. This sp can be compiled and called without any error but it doesnât seem
to execute. I donât get the expected results.
I've looked at the job in the SQL Server Agent on the server and the job does not appear to have been created.
Can anyone suggest where I'm going wrong?
Code:
Use msdb;
GO
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'sp_create_overnight_schedule' AND type = 'P')
DROP PROCEDURE sp_create_overnight_schedule
GO
CREATE PROCEDURE sp_create_overnight_schedule
AS
BEGIN
IF NOT EXISTS(SELECT 'ADAPT_OVERNIGHT')
BEGIN
EXEC dbo.sp_add_job
@job_name = N'ADAPT_OVERNIGHT',
@enabled = 1,
@description = N'Daily update of status of Adverts',
@start_step_id = 1,
@notify_level_eventlog = 3,
@delete_level = 0,
@owner_login_name = N'Administrator'
EXEC dbo.sp_add_jobserver
@job_name = 'ADAPT_OVERNIGHT'
END
IF NOT EXISTS(SELECT 'UPDATE ADVERT STATUS')
BEGIN
EXEC sp_add_jobstep @job_name = N'ADAPT_OVERNIGHT',
@step_id = 1,
@step_name = N'UPDATE ADVERT STATUS',
@server = N'BISUKV11DB02\sql2005',
@database_name = N'BDOM1',
@database_user_name = N'dbo',
@output_file_name = N'C:\TEMP\ALEXP.txt',
@command = N'EXEC sp_update_advert_status'
END
IF NOT EXISTS(SELECT 'OVERNIGHT SCHEDULE')
BEGIN
EXEC sp_add_schedule
@schedule_name = N'OVERNIGHT SCHEDULE',
@enabled = 1,
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 1,
@active_start_time = 200000,
@active_end_time = 235959,
@owner_login_name = N'Administrator',
@schedule_id = 1
EXEC sp_attach_schedule
@job_name = N'ADAPT_OVERNIGHT',
@schedule_name = N'OVERNIGHT SCHEDULE'
END
END
Stored Procedure that creates a job, jobstep and schedule in SQL Server
