Collation conflict SQL Server 2008

Question

Hello,

I've been going around this but I haven't found a solution for my problem. My sql query is:

SELECT 
   dbo.Country.CtyRecID, dbo.Country.CtyShort, dbo.Notification.NotRecID,
   dbo.Notification.NotName, dbo.TemporalSuspension.TCtsCode, 
   dbo.TemporalSuspension.TCtsCodeRecID,
   dbo.TaxPhylum.PhyName AS Taxon, dbo.TemporalSuspension.TCtsNotes, 
   dbo.TemporalSuspension.TCtsRecID,
   dbo.TemporalSuspension.TCtsKgmRecID, 
   CASE dbo.TemporalSuspension.TCtsKgmRecID WHEN 1 THEN 'Animals'
         WHEN 2 THEN 'Plants' ELSE 'All' END AS Kingdom
FROM  
   dbo.TemporalSuspension 
INNER JOIN dbo.Notification 
   ON dbo.TemporalSuspension.TCtsStartNotRecID = dbo.Notification.NotRecID 
INNER JOIN dbo.Country 
   ON dbo.TemporalSuspension.TCtsCtyRecID = dbo.Country.CtyRecID 
INNER JOIN dbo.TaxPhylum 
   ON dbo.TemporalSuspension.TCtsCodeRecID = dbo.TaxPhylum.PhyRecID 
      AND dbo.TemporalSuspension.TCtsCode LIKE 'PHY'

UNION ALL

SELECT 
    dbo.Country.CtyRecID, dbo.Country.CtyShort, dbo.Notification.NotRecID, 
    dbo.Notification.NotName, dbo.TemporalSuspension.TCtsCode, 
    dbo.TemporalSuspension.TCtsCodeRecID, 
    dbo.TaxClass.ClaName AS Taxon, dbo.TemporalSuspension.TCtsNotes, 
    dbo.TemporalSuspension.TCtsRecID, 
    dbo.TemporalSuspension.TCtsKgmRecID, 
    CASE dbo.TemporalSuspension.TCtsKgmRecID WHEN 1 THEN 'Animals' 
        WHEN 2 THEN 'Plants' ELSE 'All' END AS Kingdom
FROM  
   dbo.TemporalSuspension 
INNER JOIN dbo.Notification 
   ON dbo.TemporalSuspension.TCtsStartNotRecID = dbo.Notification.NotRecID 
INNER JOIN dbo.Country 
   ON dbo.TemporalSuspension.TCtsCtyRecID = dbo.Country.CtyRecID 
INNER JOIN dbo.TaxClass 
   ON dbo.TemporalSuspension.TCtsCodeRecID = dbo.TaxClass.ClaRecID 
      AND dbo.TemporalSuspension.TCtsCode LIKE 'CLA'

But I don't understand why it doesn't work, I keep getting this error :

Cannot resolve collation conflict for column 7 in SELECT statement.

What's wrong? I've used this other times and I never got this problem. According to the error the dbo.TaxPhylum.PhyName AS Taxon, and dbo.TaxClass.ClaName AS Taxon, is the thing giving the problem, but I don't really understand why, both columns have the same type and everything.

EDIT: This is the result obtained with the query, how do I get around this?

Column Name Table Name  collation_name
PhyName vDecisionsExpanded  Latin1_General_CI_AS
ClaName vDecisionsExpanded  SQL_Latin1_General_CP1_CI_AS

thanks

Answer 1

Different server/database combination? At the risk of asking the obvious, I assume that you have verified your columns collation sequences?

Answer 2

Try this query in your database:

SELECT 
    col.name 'Column Name',
    OBJECT_NAME(object_id) 'Table Name',
    col.collation_name 
FROM sys.columns col
WHERE col.system_type_id IN (35, 99, 167, 175, 231, 239) -- TEXT, NTEXT, VARCHAR etc.

It will show you all string-related columns in your database, and their collation.

The error message says that column 7 is the culprit - that would be dbo.TaxPhylum.PhyName - so also check the TaxPhylum database. Is the collation in that database different from your normal database??

UPDATE: if you have a collation conflict, you can do two things:

1) if it's only a single or a few columns in a SELECT, just add the COLLATE ..... modifier to them:

SELECT 
   .....
   dbo.TaxPhylum.PhyName COLLATE SQL_Latin1_General_CP1_CI_AS AS Taxon, 
   .....

2) if it's a lot of columns, you might want to consider to modify the COLLATION on those columns / tables or in that database all together

• How to change database or server collation