if table not update in last 10 min

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

SQL Server 2005 Forums

Transact-SQL (2005)

if table not update in last 10 min

Author

Topic

avipenina
Starting Member

44 Posts

Posted - 2008-04-15 : 04:53:03

Hi,

i've table that updated every 5-10 min from a specific program.

i need a script that check if that table wasn't updated in the last 5-10 min then send email or SMS(i've SMS modem)

is it doable?

THX

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-15 : 04:55:18

Yes.

E 12°55'05.25"
N 56°04'39.16"

Artoo
Starting Member

16 Posts

Posted - 2008-04-15 : 05:00:39

You can create a table with a date field.
Then create a trigger on the table you want to monitor, and the trigger will insert GETDATE() into your table.
Then create a job which runs every 10 minutes and checks the date.

avipenina
Starting Member

44 Posts

Posted - 2008-04-15 : 05:13:53

quote:
Originally posted by Artoo

You can create a table with a date field.
Then create a trigger on the table you want to monitor, and the trigger will insert GETDATE() into your table.
Then create a job which runs every 10 minutes and checks the date.

i know how to write the first 2 things that needs.
can you help me with the script for the job that check if the dating table was not updated in the last 10 min then do something?
THX

Artoo
Starting Member

16 Posts

Posted - 2008-04-16 : 02:46:56

Let the job call a stored proc which goes a little something like this:

CREATE PROCEDURE CheckTable
AS

DECLARE @MinutesSinceUpdate int

SELECT @MinutesSinceUpdate = DATEDIFF(n, DateLastUpdated, GETDATE())
FROM TableUpdate
WHERE TableID = 1

IF @MinutesSinceUpdate > 10
BEGIN
     --Do Stuff Here
END

GO

avipenina
Starting Member

44 Posts

Posted - 2008-04-16 : 03:02:49

quote:
Originally posted by Artoo

Let the job call a stored proc which goes a little something like this:
CREATE PROCEDURE CheckTable
AS

DECLARE @MinutesSinceUpdate int

SELECT @MinutesSinceUpdate = DATEDIFF(n, DateLastUpdated, GETDATE())
FROM TableUpdate
WHERE TableID = 1

IF @MinutesSinceUpdate > 10
BEGIN
--Do Stuff Here
END

GO

Thx.
can you explain what is DateLastUpdated(is it a column in the TableUpdate table)?
what is it TableID?
and do i need to clean the TableUpdate or leave all the getdate() that inserted there?
Thx

Artoo
Starting Member

16 Posts

Posted - 2008-04-16 : 03:20:15

Hi,

My response allows you to check more than 1 table, therefore I included a TableID. If you only need to check one table then you do not need a TableID. DateLastUpdated is a datetime column in TableUpdate which is updated with your trigger.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources