0

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?

2

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 <your login> 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.

3
  • I am only passing the #schedule_id and #active_start_time parameters, here is the full command being executed: "EXEC msdb.dbo.sp_update_schedule #schedule_id=130, #active_start_time=220500" (I replaced the @ with # because Stack is complaining about notifications.)
    – Metaphor
    Oct 6 '20 at 13:31
  • This was very helpful. It now looks like it is a permissions issue. My code works on Localhost, where I am SA but not on the Dev server, where I believe I only have read on msdb. I'll check it out with the DBA.
    – Metaphor
    Oct 6 '20 at 13:47
  • @Metaphor I've updated with some comments on security for you.
    – AMtwo
    Oct 6 '20 at 13:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.