搜尋本站文章

2011-04-23

無法解析 equal to 作業中...之間的定序衝突(Cannot resolve the collation conflict between...in the equal to operation) :當資料行的定序不相同,資料表聯結無法正常執行(2)

資料表聯結(Table Join)對象是「暫存資料表(Temporary Table)」或是「資料表變數(Table Variable)」時,會有以下的注意事項:

(一) 「暫存資料表(Temporary Table)」
定序與 tempdb
tempdb 資料庫在每次 SQL Server 啟動時建立,且預設定序與 model 資料庫相同。

通常與執行個體的預設定序相同。

如果建立使用者資料庫,並指定與 model 不同的預設定序,使用者資料庫的預設定序就會與 tempdb 不同。

所有暫存預存程序或暫存資料表會在 tempdb 中建立及儲存。

這表示暫存資料表中所有隱含的資料行,與暫存預存程序中所有可強迫的常數、變數與參數,都會與建在永久資料表和預存程序中的同等物件具有不同的定序。

(二) 「資料表變數(Table Variable)」
若未指定 collation_definition,資料行就會繼承目前資料庫的定序。


請參考以下的範例程式碼:

(一) 「暫存資料表(Temporary Table)」
-- 傳回 SQL Server 支援的全部「定序(collation)」之清單。
SELECT name N'定序', description N'描述' 
FROM fn_helpcollations()
-- WHERE name LIKE 'Chinese_Taiwan%'



-- 查詢各個資料庫的「預設定序(default collation)」
SELECT name N'資料庫', collation_name N'預設定序' 
FROM sys.databases 



-- EX1. 使用暫存資料表,資料表聯結(Table Join)
USE Northwind
GO
CREATE TABLE #Customers01
( CustomerID nchar(5), ContactName nvarchar(30), Phone nvarchar(24) )
GO
INSERT #Customers01
 SELECT CustomerID, ContactName, Phone FROM Northwind.dbo.Customers
GO

-- 使用暫存資料表來執行資料表聯結,產生錯誤,因為 Tempdb 與 使用者資料庫兩者的定序是不同
SELECT *
FROM Northwind.dbo.Orders O INNER JOIN #Customers01 C
ON O.CustomerID =C.CustomerID 

/* 錯誤訊息
訊息 468,層級 16,狀態 9,行 3
無法解析 equal to 作業中 ""Chinese_Taiwan_Stroke_BIN"" 與 "Chinese_Taiwan_Stroke_CI_AS" 之間的定序衝突。
 
Msg 468, Level 16, State 9, Line 3
Cannot resolve the collation conflict between ""Chinese_Taiwan_Stroke_BIN"" and "Chinese_Taiwan_Stroke_CI_AS" in the equal to operation.
*/

DROP TABLE #Customers01
GO

--可能的解決方式
--(1) 修改暫存資料表的「定序」,使用 COLLATE database_default
CREATE TABLE #Customers01
( CustomerID nchar(5) COLLATE database_default
 , ContactName nvarchar(30), Phone nvarchar(24) )
GO

--(2) 修改暫存資料表的「定序」,使用 COLLATE Chinese_Taiwan_Stroke_CI_AS
CREATE TABLE #Customers01
( CustomerID nchar(5) COLLATE Chinese_Taiwan_Stroke_CI_AS
 , ContactName nvarchar(30), Phone nvarchar(24) )
GO

--(3) 使用 COLLATE DATABASE_DEFAULT:使 COLLATE 子句繼承目前資料庫的定序。
SELECT *
FROM Northwind.dbo.Orders O INNER JOIN #Customers01 C
ON O.CustomerID =C.CustomerID COLLATE DATABASE_DEFAULT

--(4) 使用 COLLATE 子句,轉換為定序為:Chinese_Taiwan_Stroke_CI_AS
SELECT *
FROM Northwind.dbo.Orders O INNER JOIN #Customers01 C
ON O.CustomerID =C.CustomerID COLLATE Chinese_Taiwan_Stroke_CI_AS



(二) 「資料表變數(Table Variable)」

-- EX2. 使用資料表變數
-- 資料表變數:若未指定 collation_definition,資料行就會繼承目前資料庫的定序。

DECLARE @tbCustomers01 TABLE ( CustomerID nchar(5), ContactName nvarchar(30), Phone nvarchar(24) )

INSERT @tbCustomers01
 SELECT CustomerID, ContactName, Phone FROM Northwind.dbo.Customers

SELECT *
FROM Northwind.dbo.Orders O INNER JOIN @tbCustomers01 C
ON O.CustomerID =C.CustomerID  




參考資料:

無法解析 equal to 作業中...之間的定序衝突(Cannot resolve the collation conflict between...in the equal to operation) :當資料行的定序不相同,資料表聯結無法正常執行;Msg 468, Level 16, State 9, Line 3 (1)
http://sharedderrick.blogspot.com/2011/04/equal-to-cannot-resolve-collation.html

使用定序
http://technet.microsoft.com/zh-tw/library/ms187582.aspx

設定與變更資料行定序
http://technet.microsoft.com/zh-tw/library/ms190920.aspx

COLLATE (Transact-SQL)
http://technet.microsoft.com/zh-tw/library/ms184391.aspx

資料表 (Transact-SQL)
http://technet.microsoft.com/zh-tw/library/ms175010.aspx

INF:常見問題集 - SQL Server 2000 - 資料表變數
http://support.microsoft.com/?kbid=305977