Creating a custom alert

This guide will go through the process of creating a custom alert that will trigger if there is a collation mismatch between server level collation and database level collation.

First a query is created that identifies all databases with a different collation than the server collation:

select s.server_name, d.database_name, s.collation, d.collation
from dbo.v_servers s
left join dbo.v_databases d on d.server_name = s.server_name and d.collation != s.collation
where d.database_name is not null

To create the custom alert, the logic of the above t-sql statement must be added to the dbo.alerts table in the sql column. The t-sql statement in the sql column must always return the following 3 columns: server_name, object_name, count

The query will now be rewritten to conform to the logic used in the sql column in dbo.alerts:

select s.server_name, d.database_name object_name, count(*) [count]
from dbo.v_servers s
left join dbo.v_databases d on d.server_name = s.server_name and d.collation != s.collation
where d.database_name is not null
group by s.server_name, d.database_name

The row can now be added to the dbo.alerts table:

insert into dbo.alerts (name, link, priority, priority_id, server_level, retention_days, alert_receive_count_trigger, received_within_minutes, sql, enabled, is_system)
select 'Collation mismatch', '', 'Low', 4, 0, 31, 1, 0, 'select s.server_name, d.database_name, s.collation, d.collation
from dbo.v_servers s
left join dbo.v_databases d on d.server_name = s.server_name and d.collation != s.collation
where d.database_name is not null
', 1, 0

For more information on the different columns in dbo.alerts, see Alerting.

The alert will now be shown in the dbo.v_latest_stats view:

select *
from dbo.v_latest_stats s
where s.name = 'Collation mismatch'

To create alerts based on delta values between samples, see Custom stats.