Creating DTS that check last time record was updated

• Oleg-316599

  Grasshopper

  Points: 12

  More actions

  April 4, 2006 at 6:43 am

  #167669

  Good Morning all

  I have the following situation. There is a table thats gets updated, with a time value, and i would like to set dts @ 4pm to check if the time stamp in it is more then 2hr away from now. If it is, send an emailf

  Is it better to do it with stored proc that is called from dts, or do it in vbscript ? I am not exactly sure how to go about it

  thanks

   

• Oleg-316599

  Grasshopper

  Points: 12

  More actions

  April 4, 2006 at 7:09 am

  #630647

  i came up with something like this --

  SET QUOTED_IDENTIFIER ON 
  GO
  SET ANSI_NULLS ON 
  GO

  CREATE    PROCEDURE DDD1
  AS

  DECLARE @DateDif Datetime
  Declare @LastDate Datetime

  Declare @Date1 Datetime
  Declare @Date2 Datetime
  Declare @Date3 Datetime

  --SELECT @LastDate = 'select dest_commit_time from rs_lastcommit where origin = 103'

  set @Date1 = '2006-04-03 17:00:10.373'
  set @Date2 = '2006-04-03 17:00:10.373'
  --set @Date3 = Date

  set @DateDif = DateDiff(hh,@Date1,@Date2)

  print @DateDif

  GO
  SET QUOTED_IDENTIFIER OFF 
  GO
  SET ANSI_NULLS ON 
  GO

  i need smng in those lines --

   

  SET QUOTED_IDENTIFIER ON

  GO

  SET ANSI_NULLS ON

  GO

  CREATE    PROCEDURE DDD1

  AS

  DECLARE @DateDif Datetime

  Declare @LastDate Datetime

  Declare @Date3 Datetime

  set  @LastDate = 'select dest_commit_time from rs_lastcommit where origin = 103'

  set @Date3 = Date

  set @DateDif = DateDiff(hh,@LastDate,@Date3)

  If @DateDiff >= 2 Then

       SendEmail to <A href="mailtoleg@ddd.com">Oleg@ddd.com , topic "More then 2hr diff"

  End If

  GO

  SET QUOTED_IDENTIFIER OFF

  GO

  SET ANSI_NULLS ON

  GO