Sep 082009
 

Instead of scanning the Event Log and capturing when the Receive Locations or Send Ports go down, you can use the following triggers to send out an email when they change status:

Receive Location:

CREATE TRIGGER [dbo].[ReceiveLocationChangeNotification]
   ON [dbo].[adm_ReceiveLocation]
   AFTER UPDATE
AS
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

    DECLARE @oldstatus int
  DECLARE @newstatus int
  DECLARE @ReceiveLocationName nvarchar(256)
  DECLARE @status nvarchar(10)
  DECLARE @message nvarchar(300)
  --Was the status not changed
  IF NOT UPDATE([Disabled])
  BEGIN
  RETURN
  END
  --Otherwise send out email
  select @oldstatus=(select [Disabled] from Deleted)
  select @newstatus=(select [Disabled] from Inserted)
  select @ReceiveLocationName=(select [Name] from Inserted)
  SET @message=@ReceiveLocationName+' recieve location changed from '+ case
                     when @oldstatus=-1 then 'Disabled'
                    when @oldstatus=0 then 'Enabled'
                    END + ' to ' +
                    case
                    when @newstatus=-1 then 'Disabled'
                    when @newstatus=0 then 'Enabled'
                    END
                EXEC msdb.dbo.sp_send_dbmail @recipients='person@company.com',
                @subject = @message,
                @body = @message,
                @body_format = 'HTML' ;

--  print @message

END
GO

Send Port:

CREATE TRIGGER dbo.SendPortChangeNotification
   ON dbo.bts_sendport
   AFTER UPDATE
AS
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

    DECLARE @oldstatus int
  DECLARE @newstatus int
  DECLARE @PortName nvarchar(256)
  DECLARE @status nvarchar(10)
  DECLARE @message nvarchar(300)
  --Was the status not changed
  IF NOT UPDATE(nPortStatus)
  BEGIN
  RETURN
  END
  --Otherwise send out email
  select @oldstatus=(select nPortStatus from Deleted)
  select @newstatus=(select nPortStatus from Inserted)
  select @PortName=(select nvcName from Inserted)
  SET @message=@PortName+' changed from '+ case
                     when @oldstatus=1 then 'Unenlisted'
                    when @oldstatus=2 then 'Stopped'
                    when @oldstatus=3 then 'Started'
                    END + ' to ' +
                    case
                    when @newstatus=1 then 'Unenlisted'
                    when @newstatus=2 then 'Stopped'
                    when @newstatus=3 then 'Started'
                    END
                EXEC msdb.dbo.sp_send_dbmail @recipients='person@company.com',
                @subject = @message,
                @body = @message,
                @body_format = 'HTML' ;

    --print @message

END
GO

(Did I mention the disclaimer noted on the right side of this blog?)