Skip to content

New Category – SQL Alerts

April 7, 2010

Click for a list of all 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.

About these ads

From → Dynamics GP

6 Comments
  1. Nice idea for a series or posts, Ron – very similar to Business Alerts functionality of GP, an area that doesn’t get much use. Looking forward to the real-world examples of the alerts you’re using.

  2. Ron permalink

    Andy these are very similar to Business Alerts in GP. Business Alerts is what I originally started with. The Business Alerts in GP, however, are somewhat limited, and utilizing Task Centre (again which I am not affiliated with) has opened up a whole new realm of Dynamics GP alerting. I hope you enjoy the new catagory!
    -Ron

Trackbacks & Pingbacks

  1. SQL Alerts | andy.nifong
  2. It’s Been A While « Real Life Dynamics User
  3. It’s Been A While - Real Life Dynamics User (RLDU)
  4. SQL Scripts – Purchase Order Line Item Discrepancy - Real Life Dynamics User (RLDU)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 28 other followers

%d bloggers like this: