I wanted to create a custom alert on the SQL Database when number of records with some values(considered as invalid) exceeds the expected limit.
IMPORTANT: don't forget to Restart SQL Agent to activate settings.
Similar to the article Define custom error messages in SQL Server 2005
I've defined the error
EXEC sp_addmessage 60001, 1, N'
Number of not-processed tasks %d exceed the limit on %s.'
ALTER PROCEDURE [dbo].[CheckNotProcessed]
@limit int = 10
declare @StartDate datetime
declare @EndDate datetime
declare @NotProcessedCount int
set @EndDate =GetDate()
set @StartDate =DATEADD (day ,-1, @EndDate)
select @NotProcessedCount=count(*) from dbo.[MyTBL]
where [ProcessState] <> 99999
and createdDate between @StartDate and @EndDate
declare @CurrentDBName varchar(60)
RAISERROR (60001, 10,1,@NotProcessedCount, @CurrentDBName) WITH LOG
-- Example to execute the stored procedure
EXECUTE DBO.CheckNotProcessed 10
Note that if you have more than one similar database on the same server, it's important to specify DB_NAME()
I've also created Job to call the SP on a regular basis.
Then I've created an alert using an error number (Enterprise Manager)
that this approach is very powerful and allow to send monitor regular business pricesses
, as well as get notifications
about some data conditions, that required investigation