x

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.

more ▼

asked Oct 12, 2016 at 01:04 AM in Default

avatar image

arvind_1107
1 1 2

(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

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.

more ▼

answered Oct 12, 2016 at 08:16 AM

avatar image

David Wimbush
10.7k 30 34 43

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Oct 12, 2016 at 03:13 PM

avatar image

arvind_1107
1 1 2

(comments are locked)
10|1200 characters needed characters left

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?

more ▼

answered Oct 14, 2016 at 09:03 AM

avatar image

David Wimbush
10.7k 30 34 43

(comments are locked)
10|1200 characters needed characters left

Can it be done in stored procedure(/T-sql)? If you have any sample code, could you please share it with me.

more ▼

answered Oct 14, 2016 at 12:51 PM

avatar image

arvind_1107
1 1 2

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 14, 2016 at 01:41 PM

avatar image

David Wimbush
10.7k 30 34 43

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1154
x119
x102
x33
x12

asked: Oct 12, 2016 at 01:04 AM

Seen: 335 times

Last Updated: Oct 14, 2016 at 01:41 PM

Copyright 2017 Redgate Software. Privacy Policy