Update a Shared Schedule using a query

• joneden

  SSC Journeyman

  Points: 82

  More actions

  November 14, 2007 at 1:02 pm

  #176838

  I have a shared schedule that is attached to several subscriptions. I would like to update the scheduled time after our nightly routine is finished. Currently we just need to guess when the procedure will be done, and then set the schedule. Would seem to me that a person should be able to update this time using a stored proc.

  I ran a trace when I changed the time through the normal web interface, and found the steps below running. However, if I run these through MS, the schedule time changes, but the schedule does NOT execute the subscriptions.

  Please help....I am going nuts.

  Thanks,

  Jon

  exec Get_sqlagent_job_status @job_name=N'DA5D631A-888F-4705-8003-1799B22BC321'

  exec master.dbo.xp_sqlagent_notify @op_type=N'G',@job_id=default,@schedule_id=default,@alert_id=default,@action_type=default

  exec UpdateTask @Name=N'Jon_Test_Update',@ScheduleID='DA5D631A-888F-4705-8003-1799B22BC321',@StartDate='2007-11-13 01:30:00:000 PM',

  @Flags=0,@RecurrenceType=4,@State=1,@LastRunStatus=N'',@ScheduledRunTimeout=0,@NextRunTime='2007-11-14 01:30:00:000 PM',

  @LastRunTime='2007-11-14 09:55:01:820',@WeeksInterval=1,@DaysOfWeek=127

  EXECUTE msdb.dbo.sp_sqlagent_refresh_job @job_id = 0x3C47C21FBDC81A4782A79853964A8CBA

  EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters

• joneden

  SSC Journeyman

  Points: 82

  More actions

  November 14, 2007 at 1:16 pm

  #751239

  never mind....i found this in another forum.

  EXEC msdb.dbo.sp_start_job @job_name=N'DA5D631A-888F-4705-8003-1799B22BC321'

  works great.