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
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 195,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.