Translate

Wednesday, September 2, 2015

Unit4 Agresso - Re-queue IntellAgent Events

Due to a network problem that affect SMTP we had a few IntellAgent Events that timed out. So the e-mails associated with them did not get sent. I figured out a script that would re-queue the events so that the e-mails would get sent.
My script:

begin tran
INSERT INTO [dbo].[ainqueue]
   ([alert_user_id]
   ,[destination]
   ,[error_count]
   ,[event_id]
   ,[instance_id]
   ,[last_update]
   ,[output_id]
   ,[output_seq]
   ,[output_type]
   ,[result]
   ,[sort_order]
   ,[status]
   ,[user_id])
SELECT [alert_user_id]
  ,[destination]
  ,0
  ,[event_id]
  ,[instance_id]
  ,[last_update]
  ,[output_id]
  ,[output_seq]
  ,[output_type]
  ,''
  ,[sort_order]
  ,'N'
  ,[user_id]
  FROM [dbo].[ainlog]
  where  error_count = 5 AND last_update > '8/31/2015'
  delete  FROM [dbo].[ainlog]
  where  error_count = 5 AND last_update > '8/31/2015'
  commit tran

The script copies the event information from the event log table, ainlog, to the event queue table, ainqueue and then deletes the records from the log table. The events need to be deleted from the log table so that the log for the events that we just queued up get recorded when they are executed. If yo do not delete the log records the new log record will not get written because of a constraint on the table and this will prevent the queue record from being deleted which will cause the event to be ecxecuted again and anothere e-mail sent the next time the server process runs.

Note: Your TSQL from clause will need to be set to select the correct records in your particular situation.

No comments:

Post a Comment

Thank you for commenting!