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,

    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,

    • This topic was modified 5 days, 22 hours ago by  tsiryelhy.
  • 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)

  • 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.

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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.

     

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • tsiryelhy wrote:

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • @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

  • tsiryelhy wrote:

    @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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply