Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the UNION operation.

今日有朋友在寫一條TSQL 的 語句時 出現了這個 Error Message
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the UNION operation.

每當遇到 Cannot resolve the collation conflict 這個問題時
通常是因為把兩個不同編碼/排序規則 的 資料表連結在一起時出現的
以上的Error Message 說有一個Table 的Collation 是”SQL_Latin1_General_CP1_CI_AS”
而另一個是 “Latin1_General_CI_AS”

解決方法很簡單
可以在有 文字的Fields 上加上 COLLATE DATABASE_DEFAULT
這便會把 Collation轉成 資料庫默認的 模式了
和數字有關的Field 是不用加這個 Key words 的
E.G. 以AdventureWorks Database 的 Table 做例子

我先把 AdventureWorks Database 複製到另一個資料庫
之後嘗試打2個資料庫的資料連在一起

SELECT Title, FirstName, MiddleName, EmailAddress, EmailPromotion, ModifiedDate
FROM  AdventureWorks.Person.Contact
WHERE ContactID<10
UNION
SELECT Title, FirstName, MiddleName, EmailAddress, EmailPromotion, ModifiedDate
FROM  ShareChiWai.Person.Contact
WHERE ContactID>10

當你遇到
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the UNION operation.
你可以用以下方法解決

SELECT Title, FirstName, MiddleName, EmailAddress, EmailPromotion, ModifiedDate
FROM  AdventureWorks.Person.Contact
WHERE ContactID<10
UNION
SELECT Title COLLATE DATABASE_DEFAULT,
FirstName COLLATE DATABASE_DEFAULT,  MiddleName COLLATE DATABASE_DEFAULT, EmailAddress COLLATE DATABASE_DEFAULT,
EmailPromotion, ModifiedDate —由於 EmailPromotion 和 ModifiedDate 不是文字..所以不用加 COLLATE DATABASE_DEFAULT
FROM  ShareChiWai.Person.Contact
WHERE ContactID>10

有時候這可能在 Join 上面出現的
這便要在 Join 的 地方加了
E.G.

SELECT t.*
FROM Table1
INNER JOIN Table2
ON Table1.Field1 = Table2.Field1 COLLATE DATABASE_DEFAULT

Hope you find it Useful

One thought on “Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the UNION operation.

Leave a Reply