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.
First of all you need to Set up Database Mail for SQL 2005 and follow procedure
How to setup SQL Server alerts and email operator notifications
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.'
and SP:
ALTER PROCEDURE [dbo].[CheckNotProcessed] @limit int = 10 AS
declare @StartDate datetime
declare @EndDate datetime
declare @NotProcessedCount int
set @EndDate =GetDate()
set @StartDate =DATEADD (day ,-1, @EndDate)
print @StartDate
print @EndDate
select @NotProcessedCount\=count(\*) from dbo.\[MyTBL\]
where [ProcessState] <> 99999 and createdDate between @StartDate and @EndDate
if( @NotProcessedCount>@limit) begin declare @CurrentDBName varchar(60) set @CurrentDBName=DB_NAME() RAISERROR (60001, 10,1,@NotProcessedCount, @CurrentDBName) WITH LOG END /* -- ============================================= -- 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)
I fould 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/debugging.