A new dataset will be required to check the condition or conditions. A script will need to be added to that Dataset to trigger the Schedule if a certain value is returned.
For this topic, we’ll refer the this dataset as the “Exception Dataset”.
The Exception Dataset can be as simple as a query to check a condition and return the value “Error” if the condition isn’t as expected. A script is then added to the dataset to check for that error and execute a specific Schedule if found.

Dataset Caching – Checking the Condition Regularly
The dataset can be scheduled to check for the condition every “N” minutes using the Cache tab. More details on caching here.
If the dataset is set to have time-out period of 10 minutes, then the query to check whether the condition exists will run every 10 minutes. The “Cache Start” and “Cache End Time” values can be used to set the window that the checks will occur.

Avoid Multiple Emails on same condition
If multiple executions of the exception schedule is not desired, keeping a log-table can help ensure that, for example, only 1 alert is sent per day.

Sample SQL
This example shows a dataset that is running against a MS SQL DB and:

  • Checks a table storing the latest Stored Procedure execution logs
  • If an error is found, a result is returned that will trigger an email. The current date is stored in a tracking table to show that an email alert was sent today
  • If an error is found on next execution, but an email as already gone out on that day, then the error is ignored.
  • Only 1 row and 1 field is returned. The field is called “RESULT” and will contain a message about whether an error was encountered or not. You could return multiple rows if you want multiple schedules to be run. To do this you’d include a SCHEDULE_ID field and return a row for each schedule you need executed. The SCHEDULE_ID could then be used to call separate schedules in the script described here.
DECLARE @ERRORS INT, @EMAIL_TODAY INT

--Simple check of a table containing logs and errors of nightly tasks
--This could be any query checking for any result such as sales < expected or similar.
SET @ERRORS = (SELECT COUNT(*) FROM LGX_LATEST_JOB_LOGS WHERE	 ERROR_MSG IS NOT NULL)

--Has email been sent today?
--Check a LGX_ERROR_EMAIL_DATES  table containing a SENT_DATE field to log whether email sent today already or not. Prevents multiple emails if desired
SET @EMAIL_TODAY = (SELECT COUNT(*) FROM  LGX_ERROR_EMAIL_DATES WHERE DATEADD(dd, DATEDIFF(dd, 0, SENT_DATE), 0) = DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0))

--If there are errors, but no email has yet been sent, return the Error string which is checked in the script and will execute the schedule immediately
IF @ERRORS > 0 AND @EMAIL_TODAY = 0 

 BEGIN   
   --If errors, log the date in the tracking table.
   INSERT INTO LGX_ERROR_EMAIL_DATES
   VALUES (DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)) 

   --This is the value the script will execute the schedule on
   SELECT 'Error Found in Query' AS RESULT

 END

  --If errors, but email already sent, just return error and take no action
ELSE IF @ERRORS > 0 AND @EMAIL_TODAY > 0 

   SELECT 'Errors, Email Sent Today Already' AS RESULT

ELSE
  --No Errors, no action
   SELECT 'No Errors' AS RESULT

 --Optional to remove old dates from email tracking table
DELETE FROM LGX_ERROR_EMAIL_DATES  
WHERE DATEADD(dd, DATEDIFF(dd, 0, SENT_DATE), 0) <> DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)
Revision: 1
Last modified: Oct 14, 2019

Feedback

Was this helpful?

Yes No
You indicated this topic was not helpful to you ...
Could you please leave a comment telling us why? Thank you!
Thanks for your feedback.

Post your comment on this topic.

Post Comment