Sitt Trigger Monitoring Query - monitoring a date field

• LoosinMaMind

  Hall of Fame

  Points: 3366

  More actions

  November 26, 2010 at 7:48 am

  #243218

  Hi

  Quick overview, we have a Data Warehouse that is updated from another database via SitTriggers every 10 mins. The problem is that sometimes the triggers can fail due to invalid data being brought over. (this is a seperate issue and mainly down to poor database design and users not sticking to rules)

  I can monitor the status of the updates via a table (example below) and i know that if the datetime field is within the last 30 mins then everything is OK. When an error occurs the sitDateTime field freezes at the last sucessful run and this how we know there is a problem.

  Create table mfSitPosition

  (

  sitPosition int,

  sitDateTime datetime,

  )

  Go

  Insert into mfSitPosition values (9932954,GETDATE () )

  --NOTE i have used getdate () to simply put an example time in the field. The SitDateTime field will actually be updated about every 10 mins in the real table.

  I need to create a query that will look at this field and calculate if the time is <= 30 minutes ago.

  I will then add this into a job and the have some sort of alert to make me aware if the time has stopped.

  Any solution to the query would be appreciated.

  If anyone has a better solution than the alerts route then please let me know.

  Thanks in advance

• ChazMan

  SSCrazy

  Points: 2259

  More actions

  November 29, 2010 at 1:03 pm

  #1256458

  i believe you are looking for the datediff() function.

  Try this:

  DECLARE @LastRun DateTime

  select @LastRun = max(sitDateTime) from mfSitPosition

  if datediff(mi,@LastRun,getdate()) > 30

  BEGIN

  EXEC yourstoredprocname

  END

  --------------------------------------------------------------------------
  When you realize you've dug yourself into a hole....Step 1...stop digging.