Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 5 down vote accepted

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;
share|improve this answer
    
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 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 at 13:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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