May 3, 2021 at 12:28 pm
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,
there are no add 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 add 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 add 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 add in the table => Alert
Would it be possible to do this?
Thanks in advance friends,
May 3, 2021 at 1:32 pm
Assuming you have a CreatedDateTime or equivalent that is always populated (e.g., has a default value & nobody explicitly sets it to null), yes, it seems possible.
You'd probably use a SQL Server Agent job that runs on your specified schedule, queries to see if there are any CreatedDateTime values between PeriodStartTime and PeriodEndTime, and then execute sp_send_dbmail (assumes you've enabled & configured database mail)
May 3, 2021 at 4:22 pm
How can you tell if there is more data in the table? You can count rows, but if someone adds a row and deletes a row, you won't be alerted.
As noted, once you have a way to determine if data has been added, and you can write a query that returns some value to tell you if data has been added, you can schedule this query as an alert.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 3, 2021 at 7:47 pm
Make it easy on yourself... add a trigger to the table to count "what's happening" to the table and capture it in a "what happened at what time" history table. Then, setup a job to run every N number of minutes to interrogate that table. This would be a great place to have a clustered index in descending order based on the temporal column that contains "when it happened".
Done correctly, you won't even notice the trigger is active.
--Jeff Moden
May 3, 2021 at 10:58 pm
I would create a job that runs every 5 minutes with code in it something like this:
DECLARE @CurrentDateTime as datetime = GETDATE()
IF NOT EXISTS(SELECT *
FROM dbo.OnData d
WHERE d.DateStamp > DATEADD(mi,-30, @CurrentDateTime ))
BEGIN
-- Code here to raise alert
SELECT 'Alert no rows from ' + LEFT(CONVERT(varchar, DATEADD(mi,-30, @CurrentDateTime ), 121),20) + ' to ' + LEFT(CONVERT(varchar, @CurrentDateTime, 121),20)
END
The job should start at 8:30 am and finish at closing time.
May 4, 2021 at 3:42 pm
Let's hope they have such a temporal column available on the table and that it defaults to the current date and time on insert.
--Jeff Moden
May 4, 2021 at 3:45 pm
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, there are no add 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 add 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 add 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 add in the table => Alert
Would it be possible to do this?
Thanks in advance friends,
"Enquiring minds want to know"... do you actually have a temporal column on the table that defaults to the current date and time or not?
--Jeff Moden
May 5, 2021 at 3:55 pm
@jeff Moden
No I don't have this column in my table, just a datetime column for the date and time of an added row
Ok... that's kind of the same thing. Just stand up a job that checks what the max value in that column is once every x number of minutes, as the others have suggested.
--Jeff Moden
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply