Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- IF EXISTS(SELECT TOP 1 1 FROM sys.triggers WHERE name = 'ILC_BGTaskHistory_mstAfterUpdate')
- DROP TRIGGER [dbo].[ILC_BGTaskHistory_mstAfterUpdate]
- GO
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TRIGGER [dbo].[ILC_BGTaskHistory_mstAfterUpdate]
- ON [dbo].[BGTaskHistory_mst]
- AFTER Update
- AS
- DECLARE @SiteRef dbo.SiteType = dbo.GetSiteContext()
- DECLARE @CompletionStatusBefore as varchar(100)
- DECLARE @CompletionStatusAfter as varchar(100)
- DECLARE @AlertSubjectParm1 as varchar(max)
- DECLARE @AlertMsgParm1 as varchar(max)
- DECLARE @ConfigName varchar(8)
- SELECT TOP 1 @ConfigName = [dbo].[ILC_GetConfigurationName]()
- SELECT @CompletionStatusAfter = ISNULL(CAST(CompletionStatus as VARCHAR(100)),'EMPTY')
- FROM inserted
- IF (@CompletionStatusAfter <> '0')
- BEGIN
- SELECT @CompletionStatusBefore = ISNULL(CAST(CompletionStatus as VARCHAR(100)),'EMPTY')
- FROM deleted
- IF (@CompletionStatusBefore <> @CompletionStatusAfter)
- BEGIN
- SELECT
- @AlertSubjectParm1 = AlertSubjectParm1
- ,@AlertMsgParm1 = AlertMsgParm1
- FROM
- (SELECT
- @ConfigName + ' Background Task ' + TaskName + ' has failed' as AlertSubjectParm1
- ,'Background Task ' + ISNULL(CAST(TaskNumber as VARCHAR(100)),'') + ' ' + ISNULL(TaskName,'') + ', initiated by user ' + ISNULL(RequestingUser,'') + ' failed at '
- + ISNULL(CAST(MONTH(CompletionDate) as VARCHAR(2)) + '-' + CAST(DAY(CompletionDate) as VARCHAR(2)) + '-' + CAST(YEAR(CompletionDate) as VARCHAR(4)) + ' ' + CAST(DATEPART(hh,CompletionDate) as VARCHAR(2)) + ':' + CAST(DATEPART(MINUTE,CompletionDate) as VARCHAR(2)),'')
- + ', with a Completion Status of ' + ISNULL(CAST(CompletionStatus as VARCHAR(100)),'') + ' and the error message: ' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + ISNULL(TaskErrorMsg,'') as AlertMsgParm1
- FROM inserted) NotifyPublicationSubscriber
- EXEC dbo.NotifyPublicationSubscribersSp
- @PublicationName = 'ILC_BackgroundTaskFailure'
- ,@AlertSubject = 'D=Text0'
- ,@AlertMsg = 'E=Msg'
- ,@AlertHTMLMsg = NULL -- For GenericSendEmail: @AlertHTMLMsg overrides @AlertMsg; For GenericNotify: Both can be used.
- ,@AlertSubjectParm1 = @AlertSubjectParm1
- ,@AlertMsgParm1 = @AlertMsgParm1
- ,@Category = 'IT Admin'
- END
- END
- GO
- IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ILC_GetConfigurationName]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
- DROP FUNCTION [dbo].[ILC_GetConfigurationName]
- GO
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[ILC_GetConfigurationName]
- (
- )
- RETURNS SiteType
- AS
- BEGIN
- DECLARE @ReturnValue SiteType
- DECLARE @DBName nvarchar(16) = DB_NAME()
- DECLARE @SiteRef dbo.SiteType = (select [site] from parms where parm_key = 0)
- SELECT @ReturnValue = SUBSTRING(@DBName,1,3) + SUBSTRING(@SiteRef,4,LEN(@SiteRef) - 3)
- RETURN @ReturnValue
- END
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement