Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Step fails, but job reports success. Expand / Collapse
Author
Message
Posted Friday, September 4, 2009 6:22 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 5:42 PM
Points: 1,155, Visits: 549
I have a job that has multiple steps. Each step is set to 'Go to next step' on success or failure, except for the last step which is set to 'Quit the job reporting success/failure'. If the last step fails I get an email notification. If a step other than the last step fails I do not receive an email notification.

I am looking for a way to monitor jobs so I am aware when something like this happens. As anyone run into this? Any suggestions?

I am starting to look at the msdb database tables. I am thinking of setting up a Reporting Services report that queries these tables and emails me the results daily.

Does anyone have any ideas on how I could receive a failure notification instantly? Maybe a trigger? I have very little experience with triggers.
Post #782848
Posted Sunday, September 6, 2009 6:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, October 3, 2010 3:50 AM
Points: 146, Visits: 178
Maybe on failure you should use "Quit the job reporting success" ? Or maybe you can make a query against msdb.dbo.sysjobhistory ?
Post #783447
Posted Sunday, September 6, 2009 12:13 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, December 15, 2014 4:34 AM
Points: 1,001, Visits: 13,533
Do you still actually want the job to continue if one of the steps fail? if not then just change the failure action to "quit the job reporting failure". Assuming you do want the job to continue then you would have to start querying the system tables to retreive the step status'.
Post #783507
Posted Tuesday, September 8, 2009 8:52 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 5:42 PM
Points: 1,155, Visits: 549
I am using the following trigger I found on SQL Server Central.

USE [msdb]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

----------------------------------------------------------------------------
-- Object Type : Trigger
-- Object Name : msdb..trg_stepfailures
-- Description : Send notifications on job STEP failures (trigger)
-- Author : www.sqlservercentral.com By Kenneth Singer
-- Date : August 2009
----------------------------------------------------------------------------

CREATE trigger [dbo].[trg_stepfailures]
on [dbo].[sysjobhistory]
for insert
as
declare @strMsg varchar(400),
@strRecipient varchar(128),
@copyRecipient varchar(128),
@bccRecipient varchar(128)

set @strRecipient = 'recipient@company.com'
set @copyRecipient = 'recipient@company.com'
set @bccRecipient = ''recipient@company.com'

if exists (select * from inserted where run_status = 0 and step_name <> '(job outcome)')
begin
select @strMsg =
convert(char(10),'Server') + char(58) + @@servername +
char(10) +
convert(char(10),'Job') + char(58) + convert(varchar(50), sysjobs.name) +
char(10) +
convert(char(10),'Step') + char(58) + convert(varchar(50), inserted.step_name)+
char(10) +
convert(char(10),'Message') + char(58) + convert(varchar(150), inserted.message)
from inserted
join sysjobs
on inserted.job_id = sysjobs.job_id
where inserted.run_status = 0

raiserror (@strMsg, 16, 10) with log
exec msdb.dbo.sp_send_dbmail
@recipients = @strRecipient,
@copy_recipients = @copyRecipient,
@blind_copy_recipients = @bccRecipient,
@body = @strMsg,
@subject = 'Job Failure'

end
Post #784315
Posted Tuesday, September 8, 2009 2:34 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:04 PM
Points: 724, Visits: 1,002
I had this once where it would remain as Red x that it didn't run but it had run. I ended up just adding a new job and deleting it in the end...
Post #784574
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse