Scheduling a Job - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Scheduling a Job

Author

Topic

madhulatha_b
Starting Member

22 Posts

Posted - 2006-07-04 : 02:53:08

Hi

I have written a stored procedure to remove one year old records from multiple table. Now I want to schedule this job so that it will automatically get execute based on period.

Could anyone let me know how to do this(creating schedular) in SQL server 2000.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-04 : 02:59:10

In Enterprise Manager, go to SQL Agent and Jobs. Right click on right pane and select New Job. In Steps, add the stored procedure you wrote. In Schedules, select the timing you want.

Or, If you prefer T-SQL code

BEGIN TRANSACTION

DECLARE @JobID BINARY(16),
	@ReturnCode INT

SELECT @ReturnCode = 0

IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1
	EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'

IF (SELECT COUNT(*) FROM msdb.dbo.sysjobs WHERE name = N'TestJob') > 0
	PRINT N'The job "TestJob" already exists so will not be replaced.'
ELSE
	BEGIN 
		EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT, @job_name = N'TestJob', @owner_login_name = N'AD\SELUPLN', @description = N'No description available.', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
		IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

		EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1,
			@step_name = N'Call the SP', @command = N'EXEC uspDeleteRows',
			@database_name = N'master', @server = N'', @database_user_name = N'',
			@subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0,
			@retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0,
			@on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
		IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

		EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
		IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

		EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Schedule for Job',
			@enabled = 1, @freq_type = 4, @active_start_date = 20060704, @active_start_time = 20000,
			@freq_interval = 1, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0,
			@freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
		IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

		EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
		IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
	END

COMMIT TRANSACTION

GOTO	EndSave

QuitWithRollback:
	IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources