3

We have a table where we create a record for a job and remove them once the job is complete. I would like to check if there is any records still hanging more than a an hour or so. We do maintain audit timestamps in the table. Is there in an option in anyway I can create an alert on Azure SQL based on a TSQL query or something which we help achieve the above?

s-a-n
  • 767
  • 2
  • 9
  • 27

2 Answers2

3

You can use a Condition Control of an Azure Logic App to check whether a given result set is valid. If the condition satisfies then you can send the email.

enter image description here

You can execute a stored procedure action to check records hanging more than a an hour. On below images stored procedure returns resultsets, you can use the logic for checking records still hanging more than a an hour and in the next condition you can check the result set brings records (is true) or not (is false). If it's true you can send email else not.

enter image description here

enter image description here

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Alberto Morillo
  • 13,893
  • 2
  • 24
  • 30
-1

Azure SQL alerts only support these metrics: enter image description here So we can't create an alert on Azure SQL based on a TSQL query.

I guess that you want to check if there is any records still hanging more than a an hour or so in the table. If the table has, you want it can sends you an email as alerts.

If you table is stored in SQL Server, you can create a trigger and use sp_send_dbmail to achieve this.

For more details, you can see: sp_send_dbmail

And here's a blob about: Send e-mail from a trigger

Azure SQL Database doesn't support sp_send_dbmail: enter image description here

Hope this can helps you.

Michael Freidgeim
  • 26,542
  • 16
  • 152
  • 170
Leon Yue
  • 15,693
  • 1
  • 11
  • 23
  • Yea I am aware of the above explanation and thank you for that. Hence I was wondering if anybody would have solution to work in this situation. As you said it looks like there is none! – s-a-n Jan 10 '19 at 03:19
  • @s-a-n We can wait some days. If others can give some good idea or solutions, I also could learn from. And if not, please mark my answer, thank you. – Leon Yue Jan 10 '19 at 06:09
  • Prefer to use text, rather than images – Michael Freidgeim Dec 01 '19 at 19:23