USE MASTER
GO
IF OBJECT_ID('dbo.sp_JobHistory') IS NULL
EXEC ('CREATE PROCEDURE dbo.sp_JobHistory AS SELECT 1 AS ID')
GO
ALTER PROCEDURE dbo.sp_JobHistory
@Startdate DATETIME = NULL,
@EndDate DATETIME = NULL,
@JobName VARCHAR(256) = NULL,
@StepName VARCHAR(256) = NULL,
@StepId INT = NULL,
@RunStatus TINYINT = 0,
@TempTableName VARCHAR(256) = '##JobHistory'
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF @Startdate IS NULL
AND @EndDate IS NULL
BEGIN
PRINT 'DECLARE @Startdate DATETIME = DATEADD(HOUR, -24, GETDATE()),
@EndDate DATETIME = GETDATE()
'
EXEC master.dbo.sp_ExecTemplate
@ObjectName = 'sp_JobHistory',
@PrintDefault = 0,
@DeclareReturn = 0,
@PrintReturnLine = 0,
@DoNotDeclareVar = 1
RETURN
END
SELECT @EndDate = ISNULL(@EndDate, GETDATE()),
@Startdate = ISNULL(@Startdate, DATEADD(DAY, -1, GETDATE()))
DECLARE @srundate INT = CAST(CONVERT(CHAR(30), @Startdate, 112) AS INT),
@sruntime INT = CAST(REPLACE(CONVERT(CHAR(30), @Startdate, 108), ':', '') AS INT),
@erundate INT = CAST(CONVERT(CHAR(30), @EndDate, 112) AS BIGINT),
@eruntime INT = CAST(REPLACE(CONVERT(CHAR(30), @EndDate, 108), ':', '') AS INT),
@SQL VARCHAR(MAX)
IF OBJECT_ID('tempdb..#sysjobhistory') IS NOT NULL
DROP TABLE #sysjobhistory
SELECT j.name AS job_name,
step_name,
j2.step_id,
j2.job_id,
run_date,
run_time,
MAX(sql_message_id) AS sql_message_id,
MAX(sql_severity) AS sql_severity,
MIN(instance_id) AS instance_id,
MAX(run_duration) AS run_duration
INTO #sysjobhistory
FROM msdb.dbo.sysjobhistory j2 (NOLOCK)
INNER JOIN msdb.dbo.sysjobs j (NOLOCK) ON j2.job_id = j.job_id
WHERE j2.run_status = ISNULL(@RunStatus, j2.run_status)
AND STEP_ID IN (ISNULL(@StepId, STEP_ID), 0)
AND (@JobName IS NULL
OR j.NAME LIKE @JobName)
AND (@StepName IS NULL
OR step_name LIKE @StepName)
AND ((j2.run_date = @srundate
AND j2.run_date < @erundate
AND j2.run_time >= @sruntime)
OR (j2.run_date = @erundate
AND j2.run_date > @srundate
AND j2.run_time <= @eruntime)
OR (j2.run_date < @erundate
AND j2.run_date > @srundate))
GROUP BY j.name,
step_name,
j2.step_id,
j2.job_id,
run_date,
run_time
OPTION (RECOMPILE)
IF OBJECT_ID('tempdb..#JobImport') IS NOT NULL
DROP TABLE #JobImport
SELECT a.job_name,
a.step_name,
CASE k.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 4 THEN 'progress'
ELSE CAST(run_status AS VARCHAR) + ': Unknown'
END AS StatusDesc,
CAST(CAST(run_date AS VARCHAR) AS DATETIME)
+ CAST(CAST(STUFF(STUFF(RIGHT('0000000' + CAST(run_time AS VARCHAR), 6), 3, 0, ':'), 6, 0, ':') AS TIME) AS DATETIME) AS RunDateTime,
a.run_duration,
k.MESSAGE,
CAST(m.TEXT AS VARCHAR(MAX)) AS ErrorMessage,
sql_message_id,
sql_severity,
k.run_status,
a.step_id,
a.job_id,
a.run_date,
a.run_time,
min_instance_id AS job_min_instance_id,
k.instance_id AS step_min_instance_id
INTO #JobImport
FROM #sysjobhistory a
LEFT OUTER JOIN sys.messages m (NOLOCK) ON m.message_id = sql_message_id
AND sql_message_id <> 0
CROSS APPLY (SELECT MIN(instance_id) AS min_instance_id
FROM #sysjobhistory b
WHERE a.job_id = b.job_id
AND a.run_date = b.run_date
AND a.run_time = b.run_time) kk
CROSS APPLY (SELECT run_status,
Util.dbo.StringConcat(MESSAGE, '') AS MESSAGE,
MIN(instance_id) AS instance_id
FROM (SELECT TOP 999999
b.run_status,
b.instance_id,
MESSAGE
FROM msdb.dbo.sysjobhistory b
WHERE a.job_id = b.job_id
AND a.step_id = b.step_id
AND a.run_date = b.run_date
AND a.run_time = b.run_time
ORDER BY b.run_status DESC,
b.instance_id) K
GROUP BY run_status) k
ORDER BY kk.min_instance_id,
k.instance_id
OPTION (RECOMPILE)
IF @TempTableName <> ''
BEGIN
IF OBJECT_ID(CASE WHEN @TempTableName LIKE '#%' THEN 'TEMPDB..' + @TempTableName
ELSE @TempTableName
END) IS NOT NULL
EXEC('DROP TABLE ' + @TempTableName )
EXEC('SELECT job_name, step_name, StatusDesc, RunDateTime, run_duration, MESSAGE, ErrorMessage, sql_message_id, sql_severity, run_status, step_id, job_id, run_date, run_time, job_min_instance_id, step_min_instance_id
INTO ' + @TempTableName + '
FROM #JobImport')
SET @SQL = 'SELECT job_name, step_name, StatusDesc, RunDateTime, run_duration, Util.dbo.CastXML(MESSAGE) as MessageXML, ErrorMessage, sql_message_id, sql_severity, run_status, step_id, job_id, run_date, run_time, job_min_instance_id, step_min_instance_id
FROM ' + @TempTableName
+ '
SELECT job_name, step_name, StatusDesc, RunDateTime, run_duration, FieldNum AS MgsLnNbr, Field AS MsgLine, ErrorMessage, sql_message_id, sql_severity, run_status, step_id, job_id, run_date, run_time, job_min_instance_id, step_min_instance_id
FROM ' + @TempTableName + '
CROSS APPLY Util.dbo.ParseDelimited(Message, ''
'') b'
PRINT @SQL
EXEC(@SQL )
END
ELSE
BEGIN
SELECT job_name,
step_name,
StatusDesc,
RunDateTime,
run_duration,
FieldNum AS MgsLnNbr,
Field AS MsgLine,
ErrorMessage,
sql_message_id,
sql_severity,
run_status,
step_id,
job_id,
run_date,
run_time,
job_min_instance_id,
step_min_instance_id
FROM #JobImport
CROSS APPLY Util.dbo.ParseDelimited(MESSAGE, '
') b
END
GO
EXEC sys.sp_ms_marksystemobject
sp_JobHistory
GO