SQL Server 2008 Conflict Conflicts
I got around this but I couldn't find a solution for my problem. My sql query:
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 this doesn't work, I keep getting this error:
The collation conflict for column 7 in the SELECT statement cannot be resolved.
What happened? I have used this at other times and have never had this problem. According to the error, the problem is dbo.TaxPhylum.PhyName AS Taxon and dbo.TaxClass.ClaName AS Taxon is the problem, but I really don't understand why both columns are the same type and that's it.
EDIT: This is the result from 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
thank
source to share
Try this query on 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 the columns associated with a row in your database and their mapping.
The error message states that column 7 is the culprit - it will dbo.TaxPhylum.PhyName
- so check the database as well TaxPhylum
. Is the collation in this database different from your normal database?
UPDATE: If you have a conflict of conflicts, you can do two things:
1) if it only contains one or more columns in the SELECT, just add a modifier to them COLLATE .....
:
SELECT
.....
dbo.TaxPhylum.PhyName COLLATE SQL_Latin1_General_CP1_CI_AS AS Taxon,
.....
2) if you have many columns, you might consider changing COLLATION for those columns / tables or in this database together
source to share