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.
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
Change is inevitable... Change for the better is not.
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
Change is inevitable... Change for the better is not.
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
Change is inevitable... Change for the better is not.
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
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy