I'm using the following statement to retrieve a list of triggers from the database:
select tr.name from sys.triggers tr union all select tr.name from sys.server_triggers tr;
However on my local SQL Server 2012 Express installation this fails with the error:
Cannot resolve collation conflict between "Latin1_General_CI_AS" and "Latin1_General_CI_AI" in UNION ALL operator occurring in SELECT statement column 1
I can "fix" this by changing the statement to explicitely use a different collation in the second part of the union:
select tr.name from sys.triggers tr union all select tr.name collate database_default as name from sys.server_triggers tr;
However I feel this is an ugly hack, and I don't really understand why there is a conflict in the first place. Both views ultimately retrieve the name from
sys.sysschobjs.name and thus all values should have the same collation.
So my two questions are:
- why is there a collation conflict in the first place?
- is it safe to use
collate database_defaultonly in the second part, or should I better use it for both queries (performance is not an issue for this).
Interesting enough the same query does not create a problem when using it against a SQL Server 2005 (standard edition) with the same triggers defined.