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.
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?
Best Answer
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.