Azure SQL alerts on a T sql query

Question

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?

Answer 1

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.

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.

Answer 2

Azure SQL alerts only support these metrics: 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:

Hope this can helps you.