sp_update_schedule says @schedule_id does not exist

Question

I wrote a script that reads job schedules from msdb.dbo.sysschedules and runs sp_update_schedule to change the active_start_time for a given subset of jobs. In each case, sp_update_schedule fails with the message "The specified @schedule_id(‘xxx’) does not exist". (MessageID 14262, Severity 16)

Is the schedule id in sysschedules not what it is looking for? What am I missing here?

asked 2020-10-06 by Metaphor

---

Answer

The @schedule_id parameter on sp_update_schedule does refer to the schedule_id column on msdb.dbo.sysschedules

sp_update_schedule contains a call to sp_verify_schedule_identifiers to validate the schedule, and it’s from this verification step that you are getting that error (message 14262).

EXECUTE @retval = msdb.dbo.sp_verify_schedule_identifiers 
          @name_of_name_parameter = '@name',  
          @name_of_id_parameter   = '@schedule_id',  
          @schedule_name          = @name             OUTPUT,  
          @schedule_id            = @schedule_id      OUTPUT,  
          @owner_sid              = @cur_owner_sid    OUTPUT,  
          @orig_server_id         = NULL  

If you are passing both a schedule id and a schedule name, there could be a problem in the string you are passing for the name, as that is used for additional filtering within the verification procedure.

I’d suggest doing some testing with calls directly to sp_verify_schedule_identifiers so that you can simplify your troubleshooting, and make sure you’re passing valid parameters to sp_update_schedule:

DECLARE  
  @name_of_name_parameter   VARCHAR(60) ,--= '@schedule_name',
  @name_of_id_parameter     VARCHAR(60) = '@schedule_id',
  @schedule_name            sysname     ,--= 'syspolicy_purge_history_schedule',  
  @schedule_id              INT         = 8,  
  @owner_sid                VARBINARY(85),  
  @orig_server_id           INT,  
  @job_id_filter            UNIQUEIDENTIFIER    = NULL,
  @RetCode                  INT;

EXEC @RetCode = sp_verify_schedule_identifiers 
  @name_of_name_parameter    = @name_of_name_parameter,
  @name_of_id_parameter      = @name_of_id_parameter,  
  @schedule_name             = @schedule_name,    
  @schedule_id               = @schedule_id,           
  @owner_sid                 = @owner_sid,             
  @orig_server_id            = @orig_server_id,
  @job_id_filter             = @job_id_filter;

SELECT  
   RetCode              = @RetCode,
   NameOfNameParam      = @name_of_name_parameter, 
   NameOfIdParam        = @name_of_id_parameter,  
   ScheduleName         = @schedule_name,    
   ScheduleID           = @schedule_id,
   OwnerSid             = @owner_sid,
   OrigServerID         = @orig_server_id,
   JobIDFilter          = @job_id_filter;     

Permissions may also be getting in your way. You would need, at minimum, GRANT EXEC ON sp_update_schedule TO in order to make the call you are trying to make. To do the troubleshooting step I list above, you would also need to be granted EXEC on sp_verify_schedule_identifiers–though you only need this if you execute it directly. Normally, ownership chaining would allow you to only need the top-level permission & you would implicitly have the permission to do what that procedure does. You could also be added to the SQLAgentUserRole security role in msdb, which includes other permissions as well–if you’re rescheduling jobs, you may also need/want some of those other permissions in order to accomplish your work, assuming that this is an acceptable level of access.

answered 2020-10-06 by Andy Mallon