Scheduling a Stored Procedure with SQL Server Agen

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 2005 Forums

Transact-SQL (2005)

Scheduling a Stored Procedure with SQL Server Agen

Author

Topic

deepak.venkatachalam
Starting Member

1 Post

Posted - 2008-09-26 : 07:57:30

Dear All,

How to Schedule a Stored Procedure "dbo.AutoEscalation" with Sql Server Agent.

Also i need the procedure to be executed on every 9.00 AM and stop executing on even 6.00 PM.

I dont wish to execute the procedure on national holidays and sundays.

I have the list of national holidays stored in a table "dbo.holidaymaster"

How to chech with SQL Server Agent whether today is a national holiday or not against the table dbo.holidaymaster

kindly guide me in this regard,

Regards,
deepak

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 08:09:21

[code]USE [msdb]
GO

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0)
GOTO QuitWithRollback
END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Testjob',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'Domain\deepak.venkatachalam', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0)
GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'First step',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'DECLARE @Today DATETIME

SET @Today = DATEDIFF(DAY, 0, getdate())

IF EXISTS (SELECT * FROM dbo.holidaymaster WHERE holidaydate = @today)
PRINT ''Today is holiday
ELSE
EXEC dbo.AutoEscalation
',
@database_name=N'MyDatabase',
@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0)
GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0)
GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'MyJobSchedule',
@enabled=1,
@freq_type=8,
@freq_interval=126,
@freq_subday_type=8,
@freq_subday_interval=24,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20080926,
@active_end_date=99991231,
@active_start_time=90000,
@active_end_time=175959

IF (@@ERROR <> 0 OR @ReturnCode <> 0)
GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0)
GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

IF (@@TRANCOUNT > 0
ROLLBACK TRANSACTION

EndSave:[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources