Adding Steps to Scheduled Job

Question

Adding Steps to Scheduled Job

JuanBob

SSCertifiable

Points: 5059
More actions
December 28, 2006 at 4:42 pm

#368695

If I add three steps to a Job, each with sequencial step_ids (1 - 3), will step 2 fire off when step 1 has completed, and so with steps 3+?
Each step executes a stored proc, which fires off separate stored procs.
MSSQL2000
Thanks!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 1

click advanced and select quit with failure or more to next step depending on what you want it to do. If you don have expicit failures youneed to insert reults of qurey 1 into a table check those it the begining of query 2 so you know what to execute

www.sql-library.com[/url]

JuanBob SSCertifiable Points: 5059 More actions · Answer 2

JuanBob

SSCertifiable

Points: 5059

December 28, 2006 at 5:02 pm

#680407

Thank you Jules!

JuanBob SSCertifiable Points: 5059 More actions · Answer 3

To add to this question:

The Job and JobSchedule for this job is never changed - I am only working with JobSteps. The first job is static, always step_id 1, and sends me an email with the number of steps to be processed ( max(step_id) ).

The attaching jobs come from a custom application. The step_id is automatically assigned when a new step is added. The step_name is always unique. The step will be recreated if the same name comes through. I have maxed the number of potential steps to 16.

Each time a step has finished executing successfully, I want that job to be deleted. I have put sp_delete_jobstep at the end of the stored proc that is fired upon each step, but this seems to cause the job to report failure. I also noticed that deleting the step causes the OnSuccess and OnFailure actions of remaining steps to reset to Quit With Success or Quit With Failure, respectively.

Attaching steps seems to work without any problems. The problem comes down to deleting the step. Should I delete the step from the stored proc within the Step or is there another method that makes more sense?

Thanks in advance!!

noeld SSC Guru Points: 96590 More actions · Answer 4

Why do you want to built all that logic in the Job itself. You should maintain the Job function fixed and code either on a stored procedure or on a script (vb,perl,etc) your logic/loggin strategy.

changing steps configuration "dynamically" after a job is created is cumbersome, complex and error prone.

Just my $0.02

* Noel

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 5

investigate the sysjobs tables. one of these will contain a column which stores the 'quit with success\next step' status. Bung these values into a temp table before the delete then update them back the values you have stored prior to the reset. If i remember rightly the relevant tables are stored in msdb.

www.sql-library.com[/url]

Sergiy SSC Guru Points: 110208 More actions · Answer 6

There is an easier way.

Turn on Profiler, do whatever you wamt to do manually in EM, copy SQL commands executed by EM from Profiler to QA and slightly modify them according to your needs.

I would not recommend direct access to system tables.

_____________
Code for TallyGenerator