This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

sql doubt

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. 

  • 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