sql doubt

contracer

Hi:

I´m looking for a way to execute this in sql:

select keepalive from engines;

select getdate();

if difference (in minutes) between keepalive and getdate >= 2 minutes

send me an email.

Is this possible ?

Thanks. 

david.templeton

Are you using a SQL Server trigger. If you've set up SMTP, then the following trigger might work (I haven't tested it). Be careful though. I don't know how often that table is updated.

USE [NetPerfMon]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[upd_Engines]
   ON [dbo].[Engines]
   AFTER UPDATE
AS
BEGIN
 SET NOCOUNT ON;

 IF UPDATE(KeepAlive)
 BEGIN
  DECLARE @keepAlive as datetime;
  SET @keepAlive = (SELECT inserted.PICKINGSEQ FROM inserted);
  DECLARE @liveMinutes as int;
  SET @liveMinutes = DateDiff(n, @keepAlive, GETDATE());
  IF @liveMinutes >= 2
  BEGIN
   DECLARE @serverName as varchar(50);
   SET @serverName = (SELECT inserted.ServerName FROM inserted);
   DECLARE @body1 varchar(100)
   SET @body1 = 'Server :'+ @serverName+ ' keepalive is ' +
    CAST(@liveMinutes as nvarchar(10)) + ' minutes.'
   EXEC msdb.dbo.sp_send_dbmail @recipients=';myemail@myserver.com',
    @subject = 'KeepAlive Message',
    @body = @body1,
    @body_format = 'HTML' ;
  END  
 END
END

GO