New Category – SQL Alerts

Overview

I am going to start a new category of posts called SQL Alerts.  One of the things that I use a TON in my day to day work are what I call alerts.  Basically what an alert is, is a SQL query that runs on a specified schedule (i.e. every 5 minutes, once a day, etc.) and to proactively monitor certain situations that could arise in our environment.  You may ask why do I use a schedule and not a trigger?  I don’t use triggers because of the potential problems they have with GP’s business logic, and I don’t have anything that can’t go 5 minutes without attention.

Tools

The tools needed to create your own alerts for GP (or any SQL server application) will include:

- Someone who can write SQL queries in SQL Server Studio Manager or other SQL IDE.

- A system, similar to Task Centre,  for scheduling the queries to run, then email the results of the query to someone responsible for being “in the know”, and log the alerts so they are not sent multiple times (unless you want them to be)

- A recipient to act on the alerts

I plan to write a review of Task Centre in the near future to show you how easy it is to create an alerting system with this product.

Possibilities

The possibilities are endless as to what you can alert on.  Some of the alerts I have in place and use every day are below.  Eventually I plan to have all of my alerts written up on the blog for everyone to implement themselves, starting with SOP To POP Margin Analysis (which will be posted 4/9/2010).

Some Alerts I Use Daily

- Purchase Order Line Item Discrepancy – This alert will monitor all purchase order line item pricing.  If a purchase line’s price is ever changed from our Standard Cost, then I receive an alert to investigate.

- Vendor Invoice Pricing Discrepancy – Similar to the alert above, this alert will monitor vendor invoice entry.  If a line item on the invoice is for more than what our purchase order states, then I will receive an alert to investigate.

- SOP To POP Margin Analysis – This alert will monitor all sales order to purchase order links.  If a line item’s margin, that is attached to a purchase order, is below a certain percentage (set by the SQL query), then I will receive an alert.

- Customer’s Who Require Purchase Orders – This alert will monitor a user defined field on the customer’s card to determine whether or not the customer requires a purchase order.  If they do and an order is entered for them that does not have the customer’s po number, then I will receive an alert.

- New Items Created – This alert will monitor the inventory tables and will alert me any time a new inventory item is created.

- Log Out Of GP – This alert will monitor all Dynamics GP User’s Idle Time and will alert the user to log out if the idle time surpasses the parameters set in the alert.

Again, these are just a few of the alerts that I have written.  I use the alerts to proactively notify me of issues, instead of having to “roll my sleeves up” and hunt in GP for problems.

I hope this will be some welcomed information for most people.