Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
| Search | Today's Posts | Mark Forums Read
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old April 29th, 2010, 07:34 AM
Registered User
 
Join Date: Apr 2010
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Cool Stored Procedure that creates a job, jobstep and schedule in SQL Server

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
job schedule every minute for a stored procedure dfgilto SQL Server 2005 1 February 7th, 2008 07:30 PM
SQL Server Stored Procedure doug SQL Server 2000 2 February 22nd, 2005 03:04 PM
Sql server Stored procedure ranakdinesh BOOK: Professional C#, 2nd and 3rd Editions 2 May 29th, 2004 12:08 AM
submitting Oracle stored procedure or UNIX job bigraym Classic ASP Professional 0 December 22nd, 2003 02:14 PM
submitting Oracle stored procedure or UNIX job bigraym Classic ASP Basics 0 December 22nd, 2003 11:41 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.