March 24, 2016 at 5:56 am
hi
i want to run in a job the following SQL commands
update Reports set SSIS=1 where ReportID=50
EXEC msdb.dbo.sp_start_job N'D_Reports'
update Reports set SSIS=0 where ReportID=50
is it ok to put in one step like this or do i have to put begin and end
br
March 24, 2016 at 6:24 am
Yes. No. You can put as many commands as you like in a job step. If you want to be able to run each command individually, you may find it more convenient to put each one in a separate step. Otherwise, there's no reason to separate them out. You only really need a BEGIN...END wrapper with keywords such as WHILE and IF.
John
March 24, 2016 at 7:05 am
OK
IS THIS CORRECT IN ONE STEP
begin
update Reports set SSIS=1 where ReportID=51
EXEC msdb.dbo.sp_start_job N'D_Reports'
end
begin
update Reports set SSIS=0 where ReportID=51
end
BECAUSE I AM GOING TO USE A LOT OF ReportID'S IN DIFFERENT STEPS
March 24, 2016 at 8:55 am
georgheretis (3/24/2016)
OKIS THIS CORRECT IN ONE STEP
begin
update Reports set SSIS=1 where ReportID=51
EXEC msdb.dbo.sp_start_job N'D_Reports'
end
begin
update Reports set SSIS=0 where ReportID=51
end
BECAUSE I AM GOING TO USE A LOT OF ReportID'S IN DIFFERENT STEPS
Your possible problem here is that sp_start_job runs asynchronously. Just because the exec sp_start_job step has completed does not mean that the job has finished executing when the set SSIS = 0 step runs.
If that is your requirement, you should rethink this such that the D_Reports job takes care of the updates in separate steps.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 24, 2016 at 8:56 am
And, as an aside, THERE IS NO NEED TO SHOUT when posting messages here. We're nice people.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 24, 2016 at 9:03 am
I agree with what Phil says. And, while what you posted is syntactically correct, you don't need the BEGINs and ENDs except in the circumstances I mentioned earlier.
John
March 24, 2016 at 9:17 am
sorry for the Capital letters
so
is it possible to use in one step
update Reports set SSIS=1 where ReportID=51
EXEC msdb.dbo.sp_start_job N'D_Reports'
and in the second step
update Reports set SSIS=0 where ReportID=51
thie is better?
March 24, 2016 at 9:27 am
georgheretis (3/24/2016)
sorry for the Capital lettersso
is it possible to use in one step
update Reports set SSIS=1 where ReportID=51
EXEC msdb.dbo.sp_start_job N'D_Reports'
and in the second step
update Reports set SSIS=0 where ReportID=51
thie is better?
No.
Change your D_Reports job to have an extra step at the start (SSIS = 1) and then an extra step at the end (SSIS = 0).
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 28, 2016 at 11:18 pm
So, looks like you want to execute same job for different report IDs one after another is that right?
I had similar issue once, here is how I handled.
Step 1: created a table which only stores the value like "Job in Progress" and "Job is not in progress"
if object_id('dbo.D_Reports_Status','U') is not null
Drop table D_Reports_Status
Create table D_Reports_Status (Status varchar(15))
Insert into D_Reports_Status
values ('NotInProgress')
Step 2: Updated the job "D_Reports" by adding 2 extra steps one at the beginning
and one at the end
Like, Step ID "Begin_Status_Check" should have below code
Update D_Reports_Status
set status='InProgress'
And Step ID "End_Status_Check" should have below code
Update D_Reports_Status
set status='NotInProgress'
Step 3: Your job which calls this D_Reports can just have if condition to check
if exists (select * from D_Reports_Status where status='NotInProgress')
begin
update Reports set SSIS=0 where ReportID=50
update Reports set SSIS=1 where ReportID=51
EXEC msdb.dbo.sp_start_job N'D_Reports'
end
else
begin
select * from blahblah
end
Step 4: Go to step advanced options and set On failure action to repeat every 5-10 min. for 30 times
So, I guess this makes sense but basically what we are doing is creating some place holder in database to store if inner job is in progress and before we run the outer job's each reportID step, we check if the values is saying the inner job is not in progress and only then we run the inner job by updating reportID. But if we know that the value showing that the inner job is still running then we are making that outer job's reportID step to fail ("blahblah" table do not exist in the system, so it fails) and loop through the same step for 30 times but every 5-10 minutes. You set Step 3 & Step 4 for each reportID step in your Outer job steps.
March 30, 2016 at 3:08 am
ok. I want a clarification
I create the table D_Reports_Status and in the job I add 2 extra steps one at the beginning and one at the end
My question is for step 3 ( if I have 10 report id from 50 till 51)
the step for each job ( for each report id) should be like this
Step 3: for report id=50
if exists (select * from D_Reports_Status where status='NotInProgress')
begin
update Reports set SSIS=1 where ReportID=50
EXEC msdb.dbo.sp_start_job N'D_Reports'
end
else
begin
select * from blahblah
end
Step 4: for report id=51
if exists (select * from D_Reports_Status where status='NotInProgress')
begin
update Reports set SSIS=0 where ReportID=50
update Reports set SSIS=1 where ReportID=51
EXEC msdb.dbo.sp_start_job N'D_Reports'
end
else
begin
select * from blahblah
end
and so on
and the last step should be the
And Step ID "End_Status_Check" should have below code
Update D_Reports_Status
set status='NotInProgress'
March 30, 2016 at 10:25 am
Just to be clear, Step 1 is like pre-set up (One time only configuration)
Step 2 goes into your Inner job : "D_reports"
Step 3 goes into your "outer job" which calls "D_Reports" 10 times for each report ID
Now, Outer job should have 11 steps which will be created as
Step1_ReportID50:
if exists (select * from D_Reports_Status where status='NotInProgress')
begin
update Reports set SSIS=1 where ReportID=50
EXEC msdb.dbo.sp_start_job N'D_Reports'
end
--Here, before this job begins the table: D_Reports_Status ' status will be "NotInProgress" (We configured as part of Pre-Setup) and the job "D_Reports" is in idle state. So, it will set reportID to 50 and start the job "D_Reports"
Note: Since this is first step in the job process, we do not need to check to see if the "D_Reprots" is running and so no need to add "blahblah" logic or loop through 30 times
Now, Step2_ReportID51:
if exists (select * from D_Reports_Status where status='NotInProgress')
begin
update Reports set SSIS=0 where ReportID=50
update Reports set SSIS=1 where ReportID=51
EXEC msdb.dbo.sp_start_job N'D_Reports'
end
else
begin
select * from blahblah
end
Here, from this step to Step11_ReportID60 , you need have similar logic which loops through 30 times (what ever the execution time you think will be sufficient for each D_Reports job to run each time- 30 times with 5 min interval means I am assuming the D_Reports execution time is around 30*5=150min.- please set this as it is reasonable to your job)
Note: When it started inner job in "Step1_ReportID50" then as part of D_Reports, the status will set to "InProgress" and when it comes to "Step2_ReportID51", since the status check is not "NotInProgress", it will go to BlahBlah logic and fail the step 2 till D_Reports is at last step and set the status=NotinProgress and this time, the check will be successful and updates the ReprotID to 51 and run the D_Reports and continue with same logic till Step11!
I hope this makes sense.
Thanks.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply