Help finding the SQL agent job status messages

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Help finding the SQL agent job status messages

Author

Topic

rockstar283
Yak Posting Veteran

96 Posts

Posted - 2015-03-23 : 14:59:35

I am building a report in SSRS which will show the status of all the SQL agent jobs ran on a particular day. IMHO, the status messages created in the job history are not that useful. So, I would like to show the status message which can found by following the path given below:

Integration Services Catalog -> SSISDB -> Solution Name -> Projects -> Project Name -> Packages -> Right click Package Name-> Reports -> Standard Reports -> All Executions -> Click on All Messages

If you follow the exact path, we find out the exact error related to last unsuccessful execution of the job.

Can someone please tell me how to find it?

The message from [msdb].[dbo].[sysjobhistory] is not helpful for me.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-23 : 15:09:09

For SSIS packages using the SSIS catalog I believe you'll find them in the SSISDB catalog database. Check the internal.event_messages table for starters.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

rockstar283
Yak Posting Veteran

96 Posts

Posted - 2015-03-24 : 19:05:31

Thank you for your help. After taking cues from what you said..I have come up with this:

USE [msdb]
GO
/****** Object: StoredProcedure [dbo].[SQL_Agent_Jobs_Details] Script Date: 3/24/2015 4:02:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SQL_Agent_Jobs_Details](@JobName varchar(5000)
,@LastRunDate nvarchar(20)
,@LastRunStatus varchar(50)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @SQL VARCHAR(8000)
DECLARE @NewLastRunStatus INT
-- Insert statements for procedure here

SET @NewLastRunStatus = (SELECT CASE @LastRunStatus
WHEN 'Created' THEN 1
WHEN 'Running' THEN 2
WHEN 'Canceled' THEN 3
WHEN 'Failed' THEN 4
WHEN 'Pending' THEN 5
WHEN 'Ended unexpectedly' THEN 6
WHEN 'Succeeded' THEN 7
WHEN 'Stopping' THEN 8
ELSE 9
END )

SET @SQL = '
SELECT DISTINCT
E.folder_name
,E.project_name
,S.JobName
,E.package_name
,E.environment_name
--,E.execution_id
,E.executed_as_name
--,E.operation_type
--,E.process_id
, CASE e.[status]
WHEN 1 THEN ''Created''
WHEN 2 THEN ''Running''
WHEN 3 THEN ''Canceled''
WHEN 4 THEN ''Failed''
WHEN 5 THEN ''Pending''
WHEN 6 THEN ''Ended unexpectedly''
WHEN 7 THEN ''Succeeded''
WHEN 8 THEN ''Stopping''
ELSE ''Completed''
END [Status]
,E.start_time
,E.end_time
,E.server_name
,E.machine_name
,EM.event_name
,EM.message_source_name
,EM.message_time
,case EM.[message_type] WHEN 120 THEN ''Error'' else ''TaskFailed'' END [message_type]
,EM.[message]
,EM.execution_path
FROM [SSISDB].[catalog].[executions] E
LEFT OUTER JOIN
[SSISDB].[catalog].[event_messages] EM ON E.[execution_id] = EM.[operation_id]
LEFT JOIN
[MSDB].DBO.SQL_Agent_Jobs_Packages S
ON S.PackageName = E.package_name
WHERE EM.message_type in(120,130) AND 1=1
--AND E.package_name = ''Anna_Maria_Daily_Contacts_Parent.dtsx''
'
IF @JobName IS NOT NULL
BEGIN
SET @SQL = @SQL + ' AND S.JobName IN (' +''''+REPLACE(@JobName,',',''',''')+''')'
END

IF (cast(@LastRunDate as date) IS NOT NULL AND cast(@LastRunDate as date)!='1900-01-01')
BEGIN
SET @SQL = @SQL + ' AND CAST(isnull(EM.message_time, ''1900-01-01'') AS DATE) IN ('+''''+REPLACE(cast(@LastRunDate as date),',',''',''')+''')'
END

IF @LastRunStatus IS NOT NULL
BEGIN
SET @SQL = @SQL + ' AND e.[status] IN (' +''''+REPLACE(@NewLastRunStatus,',',''',''')+''')'
END

SET @SQL = @SQL + ' ORDER BY E.start_time DESC,E.end_time DESC'

--SELECT @SQL
EXEC (@SQL)

END

You can use the above script to create a dynamic sproc which takes the above mentioned params.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources