asked
Execute SQL server job based on table value
I want to execute a sql server job based on a table value. ie i have a table called dbo.T001_ControlTable and it has a column called as "Output"(which is filled by an SSIS package, when there is a success in load job), which store value "Success" & "Failure" values.Now based on that value i want to execute a job. i.e if value is "Success" then run a sql server job else run a different job. Could you please provide me a stored procedure for such activity.
Set up the two jobs without any schedules. Set up a third (scheduled) job that reads the table, decides which job to run, and uses msdb.dbo.sp_start_job to start that job.
Thanks for the reply David. The way it is currently set up is there is a job which has 6 steps in it, the first 3 steps should be dependent on flag value of a table(if status = ''y) to run the job. If any of the 3 steps is failed in the then update the control table with Y and run it again else proceed to next series of steps. The skeleton is(with my process name) : **SF to CDM** -- This is dependent on flag value, say if step 2/step 3 is failed then set the flag value to Y and run it again. Step 1 Step 2 Step 3 **CDM to CIS** Step 1 Step 2
SQL Agent doesn't give you much flow control like dynamically deciding where to go after each step, re-running the step etc. It's not the right tool for this kind of thing. You might want to have a look at SSIS?
Can it be done in stored procedure(/T-sql)? If you have any sample code, could you please share it with me.
I'm not really clear what you are trying to do or which part you are finding difficult. You know much more about what you are trying to achieve. Why don't you try it and come back with a specific question if a part of this is still giving you trouble.
20 People are following this question.
