January 25, 2013 at 11:23 am
Yeah... For somereason I was putting job name but not the job id......:w00t:
So this script will check if the job's last run status is successful, then it will move forward and if not it will wait....Right?
January 25, 2013 at 12:10 pm
newdba_sql (1/25/2013)
Yeah... For somereason I was putting job name but not the job id......:w00t:So this script will check if the job's last run status is successful, then it will move forward and if not it will wait....Right?
Yes. You should tweak as needed. Note the test is for last executed step id...the sysjobactivity table logs the final outcome of the job. So, if you have a job that has 4 steps in it, you will find the 4 steps in the sysjobhistory table, but you can find the final step executed in the sysjobactivity table by fetching the "last_executed_step_id" (which is what my script does). Again, just tweak as needed to suit your needs 🙂
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 25, 2013 at 12:18 pm
MyDoggieJessie (1/25/2013)
newdba_sql (1/25/2013)
Yeah... For somereason I was putting job name but not the job id......:w00t:So this script will check if the job's last run status is successful, then it will move forward and if not it will wait....Right?
Yes. You should tweak as needed. Note the test is for last executed step id...the sysjobactivity table logs the final outcome of the job. So, if you have a job that has 4 steps in it, you will find the 4 steps in the sysjobhistory table, but you can find the final step executed in the sysjobactivity table by fetching the "last_executed_step_id" (which is what my script does). Again, just tweak as needed to suit your needs 🙂
And be prepared for it to mis-fire many times in the coming weeks/months.
First the job will be dropped and re-created, causing the job_id to not match, and the code to fail.
Then later the job name itself will change, causing the job name to not match, and the code to fail.
Then later the job schedule(s) will change, causing the job to start / not start when it was supposed to.
Yep, you have a lot to look forward to :-).
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 25, 2013 at 12:35 pm
Of course, could happen 😀 One should takes measures to avoid that...I'd create a function to look up the job_id based upon the job name (that way no matter what the server, or whether it's dropped or re-created, it'll all still work)
Create a scalar function to look up the job_id based upon the SQL Agent Job Name
CREATE FUNCTION [dbo].[fx_FetchSQLAgentJobID] (
@JobName char(250)
)
RETURNS uniqueidentifier AS
/*
Purpose : Returns the job_id for the @job_id parameter of the msdb.dbo.sp_start_job procedure
*/
BEGIN
RETURN (SELECT job_id from msdb.dbo.sysjobs WHERE name = @JobName )
END
GO
Next check the job status based upon the job_id
CREATE FUNCTION [dbo].[fx_FetchJobStatus] (
@JobID uniqueidentifier
)
RETURNS int AS
/*
Purpose : Returns the status of a given SQL Agent Job
*/
BEGIN
RETURN (SELECT TOP 1 ISNULL(last_executed_step_id, 0)
FROM msdb.dbo.sysjobactivity
WHERE job_id = @JobID
ORDER BY run_requested_date DESC )
END
GO
Put them both together by changing the original code I posted to use both functions together:
WHILE (SELECT dbo.fx_FetchJobStatus(dbo.fx_FetchSQLAgentJobID('YourSQLAgentJobName'))) = 0
BEGIN
WAITFOR DELAY '00:05:00' -->> Recheck after a specified period of time
END
--> Run what should run if the job above isn't currently executing
PRINT 'Good to go!'
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 25, 2013 at 12:38 pm
And Yes, changing the job name itself would still cause an issue, however hopefully changes like that would be made by a DBA or otherwise communicated that a change was made, so the code could be corrected before failures occurred.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 25, 2013 at 12:46 pm
MyDoggieJessie (1/25/2013)
And Yes, changing the job name itself would still cause an issue, however hopefully changes like that would be made by a DBA or otherwise communicated that a change was made, so the code could be corrected before failures occurred.
How would one know "the code" existed that checked a particular job name??
At any rate, we'll just have to disagree on this.
I think it's a fundamental mistake to conflate the task completion with the mechanism used to complete it.
In this case, the job. It should not be required knowledge of other tasks that a job even existed, let alone what it was named. All a future task(s) should need to know is whether the specific, prerequisite task is complete or not. For example, say a stored proc in a different job later replaces the current job. With the current approach, you have to completely recode ALL dependencies for all tasks from that job. That design approach makes no sense to me.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 25, 2013 at 12:59 pm
Scott, for the record I agree with what you're saying (really, I do), just wanted to provide an option to what the OP originally requested. To fulfill his/her initial request, this would work.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 25, 2013 at 1:20 pm
ScottPletcher (1/25/2013)
MyDoggieJessie (1/25/2013)
And Yes, changing the job name itself would still cause an issue, however hopefully changes like that would be made by a DBA or otherwise communicated that a change was made, so the code could be corrected before failures occurred.How would one know "the code" existed that checked a particular job name??
At any rate, we'll just have to disagree on this.
I think it's a fundamental mistake to conflate the task completion with the mechanism used to complete it.
In this case, the job. It should not be required knowledge of other tasks that a job even existed, let alone what it was named. All a future task(s) should need to know is whether the specific, prerequisite task is complete or not.
For example, say a stored proc in a different job later replaces the current "Job A". With the current approach, you have to completely recode ALL dependencies for other tasks ("Job B", etc.).
Or say one day "Job A" fails so "Martha in Accounting" loads the records by hand. Now, how do you "tell" "Job B", etc., that, this one time, they don't require "Job A"?
That design approach makes no sense to me.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 25, 2013 at 1:42 pm
I know putting such code in job is not right but the job I am working on runs on weekend and since it is off hours so I wanted to create such step that I dont have to check it..
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply