I have a SQL Server 2005 database that I am trying to execute the t-sql
statement listed below.

I received collation error listed below from the query.

Please help me modify the t-sql statement listed below to correct the error.

Thanks,

Select type, name, parent_class_desc
from sys.triggers
Where parent_class_desc = 'DATABASE'
UNION
Select type, name, parent_class_desc from sys.server_triggers
Where parent_class_desc = 'SERVER'

Error Message:
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS"
and "Latin1_General_CI_AS" in the UNION operation.

A collation conflict error means that expressions have different collations
and cannot therefore be compared.  In this case, the database collation is
different than the server default collation so the name column in
sys.triggers has a different collation than the name column in
sys.server_triggers.  You can get the query to run by specifying a matching
collation using a COLLATE clause like the example below.

SELECT type,
    name COLLATE Latin1_General_CI_AS,
    parent_class_desc
FROM sys.triggers
WHERE parent_class_desc = 'DATABASE'
UNION
SELECT type,
    name,
    parent_class_desc from sys.server_triggers
WHERE parent_class_desc = 'SERVER';

IMHO, it's best to standardize on a single collation in your environment if
at all possbile.  Mixed collations are a pain to work with.

Show quoteHide quote

In addition to Dan's comment, a general best practice whenever the two sides
of a union can't possibly have duplicates (like in your case where the first
half always return parent_class_desc = 'DATABASE' and the second half always
returns parent_class_desc = 'SERVER'), you should use UNION ALL instead of
UNION.  This will return the same result, but run more efficiently.

Tom

Show quoteHide quote

Cursor Logic
IN keywork with > 1 column?
Still Restoring...
Backup and restore database to another server
Big deletion is filling transaction log
I HAVE A PROBLEM IN SQL
SQL 2K Defrag Utility Recomendation?
Help on Sql Server 2005 job steps lost
SSMS Open Table Include Column Headers
LogFile size - general information