2

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:

  1. why is there a collation conflict in the first place?
  2. is it safe to use collate database_default only 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.

5

sys.server_triggers is a server view, so it has the server collation. sys.triggers is a database view, so it has the database collation. You have a DB that has a collation different from the server. QED. Repro:

create database foo collate Latin1_General_CI_AI

use foo

select tr.name
from sys.triggers tr
union all
select tr.name
from sys.server_triggers tr;
  • Ah! Obvious once you know it :) Which also answers my second question: using the database collation for the the result from the "server view" should be safe, right? – a_horse_with_no_name Aug 20 '14 at 13:09
  • Should be safe, as long as you don't go cross-checking DBs (... from db1.sys... union from db2.sys...) – Remus Rusanu Aug 20 '14 at 13:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.