1

I am working on a system that generates a number of recurring emails as parts of various jobs scheduled in SQL Server Agent. The emails contain text-based reports that are loosely associated to the subject matter of their jobs, but not dependent on the job's outcome. When a customer requests a change to the contents of the report or the distribution list, it can be difficult to find the specific job/step that is responsible for generating the email/report.

To make maintenance of these tasks easier, I want to create a new central job and move each email/report step into that job. I would configure each step of the job to "Quit the job reporting success." I would configure a unique schedule for each step in the job so that the email/reports continue to be generated on their normal schedules. For instance, step 1 would run daily at 7am. Step 2 would run on Saturdays and Sundays at noon. And so on.

enter image description here

When I went to create a schedule, I was unable to find a way to specify which step to start on. I know that msdb.dbo.sp_start_job accepts a @step_name parameter. Likewise, SQL Server Agent allows me to pick a step to start from when manually starting a job through the UI. Is there a way to specify the start step from a schedule, or am I limited to starting with the configured (default) start step as listed in the job?

4

A schedule has no dependencies on a job. In fact, a schedule is a shared resource which can be used by several jobs.

There's no such thing as a "local" schedule. There's nothing stopping you, of course, from having only one job using a schedule.

I.e., there is no place in the Agent architecture for a schedule to kick off a certain job step.

So, yes, you are limited to having a schedule starting the job at the step defined in the job.

The way I read your scenario, there is nothing in common between the steps except that they share the same subsystem of your application (or however you would phrase it). I'd love for agent to have some namespace/folder functionality for jobs, but no. IMO, the closest you can come is to have a healthy naming standard for your jobs, using some prefix in the job name to identify which system each set of jobs belong to. And, of course, comments inside the job.

2
  • So I can think of a schedule as a sort of alarm clock. It doesn't care (or even know) who it wakes up; it only cares about going off when it's set to go off. It's then up to me to pick which jobs wake up for each alarm. – mathewb Sep 22 '20 at 18:10
  • Yep, that's one way to think if it. – Tibor Karaszi Sep 22 '20 at 19:10
1

Schedule is independent entity. We can not map it with the job steps. But you can achieve your goals in couple of others ways.

  1. Create another job and have a step to run your required job as below. And schedule it as per your wish.

    EXEC msdb.dbo.sp_start_job N'job_name' , 'step_name'
    
  2. As part of your job step, check current date and time, run or skip that code accordingly to your need.

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.