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.

thanks