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

Transact-SQL (2005)

scheduler

Author

Topic

yadhu_cse
Constraint Violating Yak Guru

252 Posts

Posted - 2011-04-13 : 04:47:55

Hi,
can any tel me the script to schedule stored procedure. i need a script which ll run stored procedure at 12pm.i want to create batch file,,

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-13 : 05:10:50

what are you asking exactly ?

You want the script to create a schedule job in the SQL Agent Scheduler ?

or

You want to know how to schedule a stored procedure to run at 12 PM ?

KH
[spoiler]Time is always against us[/spoiler]

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-13 : 05:56:56

You cant run your stored procedure at exactly 12.00PM or 00:00
So you need to run that sp either at 11.59Pm or 00.01Am

Raghu' S

yadhu_cse
Constraint Violating Yak Guru

252 Posts

Posted - 2011-04-13 : 06:39:46

yes i need scrpit to create ,add and exececute jobs

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-13 : 07:40:47

See comment you need to change only rows where i put --

USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'Test', --Your job Name
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@category_name=N'[Uncategorized (Local)]', --Dont worry about this
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'Test', @server_name = N'XXXXX'--Remove XXXXX and give Your server Name
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'Test', @step_name=N'Step 1', --Step Name
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXECUTE sp_who',--Here your sp in '' this will only show no of sessions connected to you instance
@database_name=N'master',
@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'Test',
@enabled=1,
@start_step_id=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@description=N'',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', --Here Login Name
@notify_email_operator_name=N'',
@notify_netsend_operator_name=N'',
@notify_page_operator_name=N''
GO
USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'Test', @name=N'Sch1',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20110413, --Here your start date
@active_end_date=99991231,
@active_start_time=110000, --This is 11 clock you need to change if you wanna to execute at 11.59 then 115900
@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO

Raghu' S

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources