將Database相容性層級調升後造成datetime欄位比對錯誤造成異常(二)

2016-11-07

這一篇比較詳細比對ODBC及OLE DB建立Linked Servers對於資料欄位變動的差異

日前將一台SQL2008R2中的一個相容性層級為90的資料庫(SQL2005搬過來的)調升成100，因為這一個動作導致AP程式異常。而最終原因是跟Linked Server有關，在此紀錄一下問題並分享給大家。

既然是跟Linked Server有關，當然要稍微提一下建立連結伺服器時須使用的ODBC及OLE DB等Driver，我先畫一張關聯圖如下，讓大家大概知道甚麼樣的選項是用哪一種Provider。ODBC就是用MSDASQL而OLE DB則是用SQLNCLI11。

這一個LAB我會利用ODBC及OLEDB對一固定SQL Server建立3種不同資料連線Driver的Linked Server。ODBC會有兩種，如下圖顯示。

一個叫SQL Server。

一個叫SQL Server Native Client 11.0。

下圖是我針對兩種ODBC Driver建立完成的ODBC連線。

一個命名為SQLSRV (ODBC Driver : SQL Server)。

一個命名為SNAC (ODBC Driver : SQL Server Native Client 11.0)。

接下來我就利用剛剛建立的ODBC連線在SQL Server建立兩個Linked Server。注意我這邊選的Provider是Microsoft OLE DB Provider for ODBC Driver(下圖紅色圈選處)。

一個命名為ODBC_SQLSRV (ODBC Driver : SQL Server)。

一個命名為ODBC_SNAC (ODBC Driver : SQL Server Native Client 11.0)。

利用ODBC Driver建好了2個Linked Server後，接下來再建立OLE DB的Linked Server 。如下圖所示我的Provider是選Microsoft OLE DB Provider for SQL Server。該Linked Servers命名為OLEDB_SQLNCLI11。

下圖紅色圈選處就是我剛剛建立的3台Linked Servers。

剛剛建立的3個Linked Servers都是連線到下圖DB1這一個資料庫，該資料庫上只有一張資料表叫tbDateTime_CL110。該資料表只有一個欄位叫chgdtime其資料型態是datetime2(7)。以上就是本次LAB的資料來源，而該資料表只有一筆資料。

下圖的CODE就是本次的LAB，步驟如下：

1 . 將TestDB的資料庫相容層級改為90 ( SQL2005 )。

2 . 利用Select Into的方式從剛剛的三個Linked Servers匯入資料到TestDB中並建立成資料表。資料表名稱分別為[ODBC_SQLSRV_C90]、[ODBC_SQLSNAC_C90]、[OLEDB_SQLNCLI11_C90]。

3 . 將TestDB的資料庫相容層級改為100 ( SQL2008 )。

4 . 利用Select Into的方式從剛剛的三個Linked Servers匯入資料到TestDB中並建立成資料表。資料表名稱分別為[ODBC_SQLSRV_C100]、[ODBC_SQLSNAC_C100]、[OLEDB_SQLNCLI11_C100]。

完成上述步驟後我們就來看看不同Driver的Linked Servers搭配不同的相容性層級的目的資料庫所建立出的6張資料表是否有所差異。

首先我們先看TestDB在相容性層級為90時所產生的3張資料表。我們可以看見資料來源是OLE DB Linked Servers建立的[OLEDB_SQLNCLI11_C90]資料表其chgdtime欄位的資料型態會跟來源一樣，還是datetime2(7)。

而利用ODBC建立的兩個Linked Servers匯入資料所建立的資料表中chgdtime欄位型態都轉變過了

[ODBC_SQLSRV_C100] 轉成 nvarchar(27)

[ODBC_SQLSNAC_C100] 轉成 datetime

我們接下來看TestDB在相容性層級為100時所產生的3張資料表又會怎樣變化。我們可以看見資料來源是OLE DB Linked Servers建立的[OLEDB_SQLNCLI11_C100]資料表其chgdtime欄位的資料型態依然還是datetime2(7)。

而利用ODBC建立的兩個Linked Servers匯入資料所建立的資料表中chgdtime欄位型態呢?

[ODBC_SQLSRV_C100] 轉成 nvarchar(27)。

[ODBC_SQLSNAC_C100] 這次跟來源資料一樣是datetime2(7)而不是datetime了。

稍微彙整一下結果，如下表：

	Driver Name	Provider	資料來源型態	相容性層級90	相容性層級100
ODBC	SQL Server	MSDASQL	datetime2(7)	navrchar(27)	navrchar(27)
ODBC	SQL Server Native Client 11.0	MSDASQL	datetime2(7)	datetime	datetime2(7)
OLE DB	SQL Server Native Client 11.0	SQLNCLI11	datetime2(7)	datetime2(7)	datetime2(7)

從表格中可以看出相容性層級調整前後會有資料欄位型態變化的就只有用ODBC中SQL Server Native Client 11.0這一個Driver建立的Linked Servers。所以我日前就是踩到了這一個雷，因此導致前端AP發生錯誤，在此感謝百敬老師、楊志強老師、Rico老師在過程之中的提點。