create an alert when updates to table stop

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

create an alert when updates to table stop

Author

Topic

DLTaylor
Posting Yak Master

136 Posts

Posted - 2009-06-01 : 09:39:52

I have an SQL 2000 database.

I have a table where inserts and edits are made to data.
Each edit \ insert is accompanied by and update to a field called ‘RowUpdateDateTime’ (datetime)

I would like to be alerted by email – when no edits have been made in a 12 hour period.

What method would you suggest I adopt.

Trigger \ alert with condition… etc…

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-06-01 : 09:52:42

scheduled task/job will do it

DLTaylor
Posting Yak Master

136 Posts

Posted - 2009-06-01 : 09:56:22

Thanks LoztInSpace - can you explian with a little more detail.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-01 : 10:29:12

Why not have a trigger doing the "RowUpdateDateTime" thingy?
No need to call the database again, is it?

E 12°55'05.63"
N 56°04'39.26"

DLTaylor
Posting Yak Master

136 Posts

Posted - 2009-06-01 : 10:55:25

Thanks Peso,

I now realise my original email should have been clearer.
I don’t wish to add a trigger to the table. (As it’s not technically my table!)

Reading around it looks like I should put some T-SQL into a Job step e.g.

SELECT
case when MAX(RowUpdateDateTime) > GETDATE() - 0.5 then 'OK' else 'ALERT' end
AS UpdateCheck
FROM [Table]

-- The above tells me if the edits are within 12 hours

can someone tell me the TSQL syntax that I could put in a job?

Im still very much guessing that this is the way SQL wants me to do this.

I cant find any really good articles on how to do this – despite this being a technique I would find useful for lots of occasions e.g. an alert when row count exceeds x

Any help is really appreciated
Thanks
Dan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-01 : 13:10:19

i think what you need is something like

SELECT @RecCnt=COUNT(1) FROM YourTable WHERE RowUpdateDateTime> DATEADD(hh,-12,GETDATE())

and then make the job sent alert when @RecCnt =0

DLTaylor
Posting Yak Master

136 Posts

Posted - 2009-06-02 : 08:40:46

Thanks visakh16,

Im getting closer now. (i hope)

i have a job step with the T-SQL;

declare @RecCnt as int

SELECT @RecCnt=COUNT(1) FROM YourTable WHERE RowUpdateDateTime> DATEADD(hh,-12,GETDATE())

how do i make the job send an alert when @RecCnt = 0 ?

thanks
Dan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-02 : 08:53:26

[code]IF NOT EXISTS (SELECT * FROM Table1 WHERE Col1 > DATEADD(HOUR, -12, GETDATE()))
EXEC xp_sendmail @recipients = 'myboss@company.com',
@subject = 'No action in Table for last 12 hours',
@message = 'There has been no activity in Table1 for the last 12 hours.'[/code]

E 12°55'05.63"
N 56°04'39.26"

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2009-06-02 : 08:54:57

You could use xp_sendmail when the condition is met, something like:

Declare @message varchar (100),
@subject varchar (200)

SELECT @subject = 'No updates in last 12 hours'
SELECT @message = 'Whatever else may be appropriate'
EXEC MASTER.dbo.xp_sendmail
@recipients = 'youremailaddress',
@subject = @subject,
@message = @message

Terry

-- Procrastinate now!

DLTaylor
Posting Yak Master

136 Posts

Posted - 2009-06-02 : 09:49:40

That’s brilliant.
Thank you all for your help.

It is now working in test. By using the 'IF NOT EXISTS...' I can control the emails. :-)

Plus - it’s that simple I’m going to be able to use the concept for other alerts.
...though im guessing you guys would probably have gone about this a different way to me :-)

Thanks
Dan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources