VIEWING THE JOB LOG
-
I'm interesting in having rights to view the job log without having to be the owner scheduled job or be logged in as SA. Any ideas how this can be done? I know you can use the SQL below to make yourself the owner, but I don't really want to be the owner of the package.
EXEC sp_update_job @job_name =
, @owner_login_name = -
Using EM you can't view all jobs, unless you are sa
Maybe this proc can help out using QA :
Use master
go
if exists (select * from dbo.sysobjects where id = object_id(N'dbo.sp_ALZ_check_failed_jobs') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure dbo.sp_ALZ_check_failed_jobs
GO
/****** Object: Stored Procedure dbo.sp_ALZ_check_failed_jobs Script Date: 20/09/2002 15:04:53 ******/
CREATE PROC sp_ALZ_check_failed_jobs
@NumDays int = 1,
@RecentOnly char(1) = 'Y',
@Job_Name varchar(132) = Null
AS
-- voorbeeldje : sp_ALZ_check_failed_jobs 5, 'N','MEStoDB2DTM_TransferSFA'
SET NOCOUNT ON
PRINT 'Checking for all jobs that have failed in the last ' + CAST(@NumDays AS char(2)) +' days.......'
PRINT ' '
if @Job_Name is null
begin
if @RecentOnly = 'Y'
begin
select ErrJobs.*
from (
SELECT
convert(datetime,substring(cast(run_date as char(8)) ,1,4) + '-' + substring(cast(run_date as char(8)) ,5,2) + '-' + substring(cast(run_date as char(8)) ,7,2)
+ ' ' +
substring(cast(RIGHT(run_time + 1000000,6) as char(6)) ,1,2) + ':' + substring(cast(RIGHT(run_time + 1000000,6) as char(6)) ,3,2) + ':' + substring(cast(RIGHT(run_time + 1000000,6) as char(6)) ,5,2)
 
AS Failure_DateTime,T2.name AS Job_Name,
T1.step_id AS Step_Nr,
T1.step_name AS Step_Name,
T1.message AS Message
FROM msdb..sysjobhistory T1
JOIN msdb..sysjobs T2
ON T1.job_id = T2.job_id
WHERE T1.run_status <> 1
AND T1.step_id > 0
AND run_date >= CONVERT(char(8), (select dateadd (day,(-1*@NumDays), getdate())), 112)
 
ErrJobsinner join
( SELECT max(
convert(datetime,substring(cast(run_date as char(8)) ,1,4) + '-' + substring(cast(run_date as char(8)) ,5,2) + '-' + substring(cast(run_date as char(8)) ,7,2)
+ ' ' +
substring(cast(RIGHT(run_time + 1000000,6) as char(6)) ,1,2) + ':' + substring(cast(RIGHT(run_time + 1000000,6) as char(6)) ,3,2) + ':' + substring(cast(RIGHT(run_time + 1000000,6) as char(6)) ,5,2)
 
) AS Max_Failure_DateTime,T2.name AS Job_Name,
T1.step_id AS Step_Nr,
T1.step_name AS Step_Name
FROM msdb..sysjobhistory T1
JOIN msdb..sysjobs T2
ON T1.job_id = T2.job_id
WHERE T1.run_status <> 1
AND T1.step_id > 0
AND run_date >= CONVERT(char(8), (select dateadd (day,(-1*@NumDays), getdate())), 112)
Group by T2.name, T1.step_id , T1.step_name
 
MaxErrJobson ErrJobs.Failure_DateTime = MaxErrJobs.Max_Failure_DateTime
and ErrJobs.Job_Name = MaxErrJobs.Job_Name
and ErrJobs.Step_Nr = MaxErrJobs.Step_Nr
and ErrJobs.Step_Name = MaxErrJobs.Step_Name
order by ErrJobs.Failure_DateTime desc
end
else --RecentOnly <> 'Y'
begin
SELECT
convert(datetime,substring(cast(run_date as char(8)) ,1,4) + '-' + substring(cast(run_date as char(8)) ,5,2) + '-' + substring(cast(run_date as char(8)) ,7,2)
+ ' ' +
substring(cast(RIGHT(run_time + 1000000,6) as char(6)) ,1,2) + ':' + substring(cast(RIGHT(run_time + 1000000,6) as char(6)) ,3,2) + ':' + substring(cast(RIGHT(run_time + 1000000,6) as char(6)) ,5,2)
 
AS Failure_DateTime,T2.name AS Job_Name,
T1.step_id AS Step_Nr,
T1.step_name AS Step_Name,
T1.message AS Message
FROM msdb..sysjobhistory T1
JOIN msdb..sysjobs T2
ON T1.job_id = T2.job_id
WHERE T1.run_status <> 1
AND T1.step_id > 0
AND run_date >= CONVERT(char(8), (select dateadd (day,(-1*@NumDays), getdate())), 112)
order by Failure_DateTime desc
end
end
else -- @Job_Name is ingevuld
begin
if @RecentOnly = 'Y'
begin
select ErrJobs.*
from (
SELECT
convert(datetime,substring(cast(run_date as char(8)) ,1,4) + '-' + substring(cast(run_date as char(8)) ,5,2) + '-' + substring(cast(run_date as char(8)) ,7,2)
+ ' ' +
substring(cast(RIGHT(run_time + 1000000,6) as char(6)) ,1,2) + ':' + substring(cast(RIGHT(run_time + 1000000,6) as char(6)) ,3,2) + ':' + substring(cast(RIGHT(run_time + 1000000,6) as char(6)) ,5,2)
 
AS Failure_DateTime,T2.name AS Job_Name,
T1.step_id AS Step_Nr,
T1.step_name AS Step_Name,
T1.message AS Message
FROM msdb..sysjobhistory T1
JOIN msdb..sysjobs T2
ON T1.job_id = T2.job_id
WHERE T1.run_status <> 1
AND T1.step_id > 0
AND run_date >= CONVERT(char(8), (select dateadd (day,(-1*@NumDays), getdate())), 112)
AND T2.name = @Job_Name
 
ErrJobsinner join
( SELECT max(
convert(datetime,substring(cast(run_date as char(8)) ,1,4) + '-' + substring(cast(run_date as char(8)) ,5,2) + '-' + substring(cast(run_date as char(8)) ,7,2)
+ ' ' +
substring(cast(RIGHT(run_time + 1000000,6) as char(6)) ,1,2) + ':' + substring(cast(RIGHT(run_time + 1000000,6) as char(6)) ,3,2) + ':' + substring(cast(RIGHT(run_time + 1000000,6) as char(6)) ,5,2)
 
) AS Max_Failure_DateTime,T2.name AS Job_Name,
T1.step_id AS Step_Nr,
T1.step_name AS Step_Name
FROM msdb..sysjobhistory T1
JOIN msdb..sysjobs T2
ON T1.job_id = T2.job_id
WHERE T1.run_status <> 1
AND T1.step_id > 0
AND run_date >= CONVERT(char(8), (select dateadd (day,(-1*@NumDays), getdate())), 112)
AND T2.name = @Job_Name
Group by T2.name, T1.step_id , T1.step_name
 
MaxErrJobson ErrJobs.Failure_DateTime = MaxErrJobs.Max_Failure_DateTime
and ErrJobs.Job_Name = MaxErrJobs.Job_Name
and ErrJobs.Step_Nr = MaxErrJobs.Step_Nr
and ErrJobs.Step_Name = MaxErrJobs.Step_Name
order by ErrJobs.Failure_DateTime desc
end
else
begin
SELECT
convert(datetime,substring(cast(run_date as char(8)) ,1,4) + '-' + substring(cast(run_date as char(8)) ,5,2) + '-' + substring(cast(run_date as char(8)) ,7,2)
+ ' ' +
substring(cast(RIGHT(run_time + 1000000,6) as char(6)) ,1,2) + ':' + substring(cast(RIGHT(run_time + 1000000,6) as char(6)) ,3,2) + ':' + substring(cast(RIGHT(run_time + 1000000,6) as char(6)) ,5,2)
 
AS Failure_DateTime,T2.name AS Job_Name,
T1.step_id AS Step_Nr,
T1.step_name AS Step_Name,
T1.message AS Message
FROM msdb..sysjobhistory T1
JOIN msdb..sysjobs T2
ON T1.job_id = T2.job_id
WHERE T1.run_status <> 1
AND T1.step_id > 0
AND run_date >= CONVERT(char(8), (select dateadd (day,(-1*@NumDays), getdate())), 112)
AND T2.name = @Job_Name
order by Failure_DateTime desc
end
end
GO
Grant execute on sp_ALZ_check_failed_jobs to public
go
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
When I try and run this I get the error message -
Server: Msg 156, Level 15, State 1, Procedure sp_ALZ_check_failed_jobs, Line 26
Incorrect syntax near the keyword 'AS'.
Server: Msg 170, Level 15, State 1, Procedure sp_ALZ_check_failed_jobs, Line 38
Line 38: Incorrect syntax near ','.
-
You may get what you want by looking at the Step properties Advanced tab (EM Job properties) and specifying that the step create a log file. This location can then be made accessible to your non-job owner.
This may not log everything you want - depends what you are after.
DB
The systems fine with no users loggged in. Can we keep it that way ?br>
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply