1 Reply Latest reply on Apr 8, 2010 12:20 PM by david.templeton

    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. 

        • Re: sql doubt
          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