question

Tsiry-0260 avatar image
0 Votes"
Tsiry-0260 asked TiborKaraszi answered

Database alert if no data added

Hi friends,

I’d like to have your help. I have a SQL Server database, and a table dbo.OnData on it.
I would like to receive an email alert from my database if:
- within a range of 30mins from 8:00:00 a.m. opening time and default time per day
- AND 30mins after the time of the last Add in the dbo.OnData table,
t*here are no additions to the dbo.OnData table, a message like "from such time to such time, No Add to dbo.OnData table".*

Example:
Opening at 08:00:00,
From 08:00:00 to 08:30:00, there is no addition in the table, => Alert

From 08:30:00 to 9:00:00, 1 line has been added at 08:35:00 => No alert

From 08:35:00 (time when the last rows were added) to 08:35:00 + 00:30:00 i.e. 09:05:00, there is no addition in the table, => Alert

From 09:05:00 to 09:35:00, there are 20 lines added, last line time at 09:15:00 => No alert

From 09:15:00 to 09:45:00, there is no addition in the table => Alert

Would it be possible to do this?

Thanks in advance friends,





sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

TiborKaraszi avatar image
0 Votes"
TiborKaraszi answered

Yes, it is possible. One way is to create a SQL Server Agent job that does a SELECT from your table and keep track of the most recently added row (you have created a table for this purpose). Then the next time this job runs (it runs every minute, for instance), it will check the most recently added row and when that was. If the time is greater then 30 minutes, then use sp_send_dbmail to send an email.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.