Skip to Content
author's profile photo Former Member
Former Member

Alerts setup

Hi all,

I have set up an alert which runs a query every 10 minutes and returns this:

I have two problems with this however...

1) The SQL actually has two SELECTs in it which I would like to call, however it is only displaying the first SELECT.

Is there a way to have multiple lines in the alert which can show me both results?

2) It runs every 10 minutes, and the newest instance will overwrite the older messages. I guess this is something to do with the name.

Is there a way to ensure that alerts are not overwritten?

Thank you all!

BBT Alert.jpg (50.3 kB)
Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Best Answer
    Posted on Sep 07, 2016 at 12:14 PM

    Hi John,

    1. If both selects have the same number of columns, you can use UNION ALL to combine them:
      SELECT 'RED ALERT'
      UNION ALL
      SELECT 'ALL CLEAR'
    2. Instead of running the alert with the query directly, you could:
      a. create a user defined table with the necessary fields,
      b. create an sql job in MS SQL Management Studio that inserts new alerts into the user defined table, preferably with a time stamp, based on your current select statements.
      c. create a new query that queries your user defined table
      d. use that query in your alert setup

    Regards,

    Johan

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi John,

      You can circumvent the different-columns problem by adding dummy columns to one or both SELECT statements.

      To get the aggregate row at the top or bottom, you can use an absolute maximum or minimum value in one of the columns and sort by that column.

      I am not sure what your query is, so looking at your screenshot here is an example pseudo-query:

      SELECT t.MdAbsEntry, t.DocNum, t.Quantity

      FROM TABLE t

      UNION ALL

      SELECT 0, CAST(GETDATE() AS NVARCHAR) + ' - Check for errors', SUM(t.Quantity)

      FROM TABLE t

      SORT BY 0 /* <- this will put the aggregate column at the top. Add DESC to get it at the bottom */

      Regards,

      Johan

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.