November 19, 2012 at 1:05 pm
Hello All,
I want to set up an alert that sends out an email if a column value of a table is less than some value. Is it possible to create an alert based on tsql?
Thanks in advance
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
November 19, 2012 at 1:25 pm
you could do it in a trigger, when some value actually gets changed, or you could create a scheduled job to periodically scan the table for exceptions, depending on how frequent you need to get the information...just schedule it for once a day, once an hour, or more frequently if need be...just make sure you use some sort of flag or data to keep track of whether you sent a notification...otherwise if you set up an alert that checks every minute, you get spammed by emails until the condition goes away.
Lowell
November 19, 2012 at 1:35 pm
Thanks for the reply Lowell. The alert is to send out an email if the column value of a table is below a specific number. I have created the job to send out an email with a specific message but I am not able to setup the alert.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
November 19, 2012 at 1:58 pm
well the details would help, but roughly it would look something like this:
if exists (select 1 from VW_products
where TotalInventory < 10
AND PRODUCTID NOT IN(SELECT PRODUCTID
FROM VW_ALL_Sent_ALERTS
WHERE DateSent <= DATEADD(day,-7, GETDATE())
BEGIN
--gather the data to send
--send the email
--save sent items to the proper view.
END
Lowell
November 19, 2012 at 2:39 pm
I created a stored proc which is as shown below:
USE [DB]
GO
/****** Object: StoredProcedure [dbo].[CheckStatLog] Script Date: 11/19/2012 16:33:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[CheckStatLog]
as
set nocount on
begin try
declare @ALERT_THRESHOLD int
set @ALERT_THRESHOLD = 50
declare @cnt int
select @cnt = percentcalled
from dbo.[StatLog]
if @cnt <= @ALERT_THRESHOLD
begin
declare @subText as nvarchar(max)
set @subText = 'Alert From Check StatLog job. The percentcalled column value is:'+ CONVERT(varchar(18),@cnt)
exec msdb.dbo.sp_send_dbmail
@profile_name = 'dbgroup',
@recipients = 'recipient@abc.def',
@body = 'Check the stat log table',
@importance = 'High',
@subject = @subText;
end
end try
begin catch
if @@trancount > 0
rollback
declare @ErrMsg nvarchar(4000), @ErrSeverity int
select @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY()
raiserror(@ErrMsg, @ErrSeverity, 1)
end catch
GO
I need the data from statlog from whose structure is as shown below.My concern here is if I already have a value of 50 or less for the column percentcalled and I have been notified of it through sp_send_dbmail. Then next time the stored proc is run and even if the new percentcalled value is not less than or equal to 50 I am still getting the email because the value sits in the table.
CREATE TABLE [dbo].[StatLog](
[StatLogId] [uniqueidentifier] NOT NULL,
[CallsPlaced] [int] NOT NULL,
[CallsCompleted] [int] NOT NULL,
[TotalPeople] [int] NOT NULL,
[PercentCalled] [int] NOT NULL,
[PercentCompleted] [int] NOT NULL,
[CallsMadeInLastFifteenMinutes] [int] NOT NULL,
[WindowStart] [datetime] NOT NULL,
[WindowEnd] [datetime] NOT NULL,
CONSTRAINT [PK_StatLog] PRIMARY KEY CLUSTERED
(
[StatLogId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
November 19, 2012 at 3:05 pm
ok, is this a special table where there is one and only one row in the table?
the reason i ask is the variable population like this:
select @cnt = percentcalled
from dbo.[StatLog]
there's no where statement...and you are assigning what could be multiple rows to a single variable...there's no way to know what value would be assigned, if there are multiple rows.\\
now, if that's the true, then you need to test whether an email has already been sent or not;
i suggest a separate table, but you can use the actual mail log.
this example is going to allow sending of mail just once a day:
ALTER procedure [dbo].[CheckStatLog]
as
set nocount on
begin try
declare @ALERT_THRESHOLD int
set @ALERT_THRESHOLD = 50
declare @cnt int
select @cnt = percentcalled
from dbo.[StatLog]
if @cnt <= @ALERT_THRESHOLD
IF NOT EXISTS(select * from msdb.dbo.sysmail_allitems mailz
WHERE mailz.profile_id IN (SELECT * from msdb.dbo.sysmail_profile where name = 'dbgroup')
and mailz.recipients = 'recipient@abc.def'
and mailz.body='Check the stat log table'
and mailz.sent_date >= DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)) --midnight today
begin
declare @subText as nvarchar(max)
set @subText = 'Alert From Check StatLog job. The percentcalled column value is:'+ CONVERT(varchar(18),@cnt)
exec msdb.dbo.sp_send_dbmail
@profile_name = 'dbgroup',
@recipients = 'recipient@abc.def',
@body = 'Check the stat log table',
@importance = 'High',
@subject = @subText;
end
end try
begin catch
if @@trancount > 0
rollback
declare @ErrMsg nvarchar(4000), @ErrSeverity int
select @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY()
raiserror(@ErrMsg, @ErrSeverity, 1)
end catch
GO
Lowell
November 19, 2012 at 3:36 pm
No this is not a special table. It has data in there luckily no value less than 50 for the column percentcalled so far.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
November 19, 2012 at 5:21 pm
sapen (11/19/2012)
No this is not a special table. It has data in there luckily no value less than 50 for the column percent called so far.
in that case, you need to fix your query;
without a where statement, you have no idea which row will be returned that has more or less than 50.
Lowell
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply