Success\Failure Notification Alert through Agent Jobs

/****** Object:  StoredProcedure [dbo].[usp_SendSuccessJobNotification]    Script Date: 15/05/2015 17:01:51 ******/

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_SendSuccessJobNotification]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[usp_SendSuccessJobNotification]

GO

/****** Object:  StoredProcedure [dbo].[usp_SendFailureJobNotification]    Script Date: 15/05/2015 17:01:51 ******/

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_SendFailureJobNotification]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[usp_SendFailureJobNotification]

GO

/****** Object:  StoredProcedure [dbo].[usp_SendFailureJobNotification]    Script Date: 15/05/2015 17:01:51 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_SendFailureJobNotification]') AND type in (N'P', N'PC'))

BEGIN

EXEC dbo.sp_executesql @statement = N'CREATE procedure [dbo].[usp_SendFailureJobNotification]

 @job_id uniqueidentifier,

 @recipients varchar(200)

as

begin

 DECLARE @job_name varchar(256)

 DECLARE @subject varchar(256)

 SELECT @job_name = name

 FROM msdb..sysjobs

 WHERE job_id = @job_id

 SET @subject = ''SQL Server Job System: ['' + @job_name + ''] Failed on ['' + @@servername + '']''

 print @subject

 EXEC msdb.dbo.sp_send_dbmail

     @recipients = @recipients,

     @body = @subject,

     @subject = @subject ;

END     '

END

GO

/****** Object:  StoredProcedure [dbo].[usp_SendSuccessJobNotification]    Script Date: 15/05/2015 17:01:51 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_SendSuccessJobNotification]') AND type in (N'P', N'PC'))

BEGIN

EXEC dbo.sp_executesql @statement = N'CREATE procedure [dbo].[usp_SendSuccessJobNotification]

 @job_id uniqueidentifier,

 @recipients varchar(200)

as

begin

 DECLARE @job_name varchar(256)

 DECLARE @subject varchar(256)

 SELECT @job_name = name

 FROM msdb..sysjobs

 WHERE job_id = @job_id

 SET @subject = ''SQL Server Job System: ['' + @job_name + ''] succeeded on ['' + @@servername + '']''

 print @subject

 EXEC msdb.dbo.sp_send_dbmail

     @recipients = @recipients,

     @body = @subject,

     @subject = @subject ;

END    

/****** Object:  StoredProcedure [dbo].[usp_SendFailureJobNotification]    Script Date: 19/02/2015 17:27:40 ******/

SET ANSI_NULLS ON

'

END

GO

Execute the above SP and then implement the below codes in to Agent job and trigger the alert for the success/Failure

declare @jobname varchar(256)

declare @jobid nvarchar(256)

set @jobname ='AgentJobname'

select @jobid = job_id

from msdb..sysjobs

where name = @jobname

exec usp_SendSuccessJobNotification @job_id = @jobid,

 @recipients ='xx@xx.com'

 declare @jobname varchar(256)

declare @jobid nvarchar(256)

set @jobname ='AgentJobname'

select @jobid = job_id

from msdb..sysjobs

where name = @jobname

exec usp_SendFailureJobNotification @job_id = @jobid,

 @recipients ='xx@xx.com'