Alerts setup

[Former Member]

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!

[Johan_H · Accepted solution]

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

[Former Member · Accepted solution]

Hey Johan, thank you very much for your reply.

Unfortunately the two SELECTs don't have the same number of columns so I don't think UNION is appropriate:

I could combine them into one select and group but then that would mess up the aggregates.

Ideally I want one line to have the aggregates and then another underneath which lists the transactions carried out before the closure of the batch, as above. Perhaps this isn't possible.

I'll try your suggestion for the second question regarding timestamping the alerts. Thanks a lot for that.

Thanks again.

Kind regards,

John

[julie_jamieson2 · Accepted solution]

In that case not is not possible, you will need two alerts.

To stop them overwriting, lust tick the "Save History" box in the Alerts Management form

[Johan_H · Accepted solution]

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