Warning on Job history
-
Hi,
I would like to know how to get (in a script) the status "WARNING" which is displayed in the job history : when a step is failed but the job is success.
thx
regards,
SC
-
Try this query, you can also modified in this...
select * from msdb..sysjobhistory
order by run_date, run_time desc
for know more abt this, you can also refer onlinebook link...
http://msdn.microsoft.com/en-us/library/ms174997.aspx
Regards,
M.I.
________________________________________
M.I.
[font="Times New Roman"]Learning is a path with no destination...[/font] -
thx, but I have already looked at this table but there is no mention of value for a "warning".
I had already made this script but it does not return the warning 😎 (only failed or success) :
select distinct @@servername 'Instance SQL Server',
[name] as 'Nom du Job',
case [enabled]
when 1 then 'Enabled'
else 'Disabled'
end as 'Activation',
cast (ltrim(str(run_date))+' '+stuff(stuff(right('000000'+ltrim(str(run_time)), 6) , 3, 0, ':'), 6, 0, ':') as datetime) as 'Dernier Run',
step_id as 'Step',
case [h].[run_status]
when 0 then 'Failed'
else 'Success'
end as 'Status' ,
STUFF(STUFF(REPLACE(STR(run_duration,6),' ','0'),5,0,':'),3,0,':') as 'Durée',
case next_run_date
when '0' then '9999-jan-01'
else cast (ltrim(str(next_run_date))+' '+stuff(stuff(right('000000'+ltrim(str(next_run_time)), 6) , 3, 0, ':'), 6, 0, ':') as datetime)
end as 'Prochain Run'
from msdb.dbo.sysjobs j
left join msdb.dbo.sysjobschedules s on j.job_id = s.job_id
join msdb.dbo.sysjobhistory h on j.job_id = h.job_id
where step_id = 0
and h.instance_id in (select max(sh.instance_id)
from msdb.dbo.sysjobs sj
join msdb.dbo.sysjobhistory sh on sj.job_id = sh.job_id
where h.step_id = 0
group by sj.name)
Can u help me
regards,
SC
-
Try to make use of message column in sysjobhistory if its capturing the warning info there.
MJ
-
hi, pls try my query in that message coloumn wil give you warning message also. or adjest in ur message column in ur query.
Regards,
M.I.
________________________________________
M.I.
[font="Times New Roman"]Learning is a path with no destination...[/font] -
thx, but where is ur query ??
the problem is that there is nothing interesting for me in the column "message" !!!
Here is an example of results for one job :
Nom du Job Step Status message
---------- ----------- ------- ------------------------------------------------------------------------------------------------------------------------
Weekly_job 0 Success The job succeeded. The Job was invoked by Schedule 23 (rr). The last step to run was step 6 (user db maintenance).
Weekly_job 1 Success Executed as user: ...
Weekly_job 2 Success Executed as user: ...
Weekly_job 3 Failed Executed as user: ...
Weekly_job 4 Success Executed as user: ...
Weekly_job 5 Success Executed as user: ...
Weekly_job 6 Success Executed as user: ...
(7 row(s) affected)
you can see the status of step 0 is "Success" while the status of step 3 is "Failed" and nothing about the "warning" in the column "message" step 0!!!:hehe: :crazy:
Regards,
SC
-
Ah, this makes sense, as far as it is concerned your job succeeded.. I'm willing to bet (money no less), that step 3 is configured "On Error: to go to next step" because of that the job didn't fail. The last step succeeded therefore the job succeeded.. You really need to rethink this if you want it to show failures..
CEWII
-
Yes, the step 3 is configured "On Error: go to next step" but what should happen.
What I want (if you look above) is the warning message (with an icon) in the "Log File Viewer" when you right click on the job and choose "View History"
Regards,
SC
-
The job is a success is what should happen. If you want it to fail then change it to On Error: exit reporting failure or something along those lines. Or you have to add extra steps to do something like add an email step between, then you configure it the normal step to On Success goto Step 3, On Error goto next step, in step 2 you email, and it is configured On Sucess goto next step, On Failure goto next step.. So in effect each step has 2 steps, 1 normal and one email..
Other than that it is behaving exactly as I would expect..
CEWII
-
Thx Elliott W but
I DONT WANT TO CHANGE MY JOB !!!
Look what I wrote before
Thx,
SC
-
Then let me put it to you this way. It is behaving they way it is supposed to, the job will succeed in this case and only in the history will it be clear. If you want a clearer indication you have to change your job. You want a script, the table you are most interested in is msdb.dbo.sysjobhistory it contains a wealth of information.. But I would go a step further and say that allowing the job to continue if usually a bad practice, not always, but usually..
Either way I'm going to bed..
CEWII
-
okiftequar (10/15/2009)
Try this query, you can also modified in this...select * from msdb..sysjobhistory
order by run_date, run_time desc
for know more abt this, you can also refer onlinebook link...
http://msdn.microsoft.com/en-us/library/ms174997.aspx
Regards,
M.I.
This should have answered your question. Look at the run_status for the individual steps.
You will need to filter it on your job_Id and then step_id. you wont see Warning you will see failure as the step failed. The codes are listed below.
0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
4 = In progress
-
Hi SC,
I researched alot on ur query but not getting a satisfied solution from sql online book also.
Let me try once if i will get then i will reply u. or else if you will get the solution so pls reply to us also.
Thanks & Regards,
M.I.
________________________________________
M.I.
[font="Times New Roman"]Learning is a path with no destination...[/font] -
Thank you for your help okiftequar.
I have no solution yet, but just an idea because if the information does not exist in a table,
must go through a script that tested every step to display the warning.
The problem is that I thought SQL stored this information in a table, otherwise it means that it is recalculated every time we go into the job history!!!
Regards,
SC
-
what tutupouet is looking for is when the step fail but the another step is success, the job itself is a success
BUT
an exclamation yellow icon appears at the job level instead of a succeed checkmark when looking at history.Which table/column identified that it should be that yellow exclamation point?
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply