之前有過將SQL2000直升SQL2014的經驗後，因此對於SQL2008R2中部分相容性層級為90的資料庫也起了調升的意念。
因此在某一日就將某部SQL2008R2中COMPATIBILITY_LEVEL是90的全調成100，同時也請AP同仁注意一下自己的系統是不是有問題。一晃眼也過了4個月，我都忘了這件事，今天突然有同是來找我並表示系統有異常。
他的系統會利用Linked Server(利用ODBC建的informix資料庫連接)及Except語法去比對兩邊某張Table的欄位然後再做處理。他表示每天比對下來要處理的筆數都超過萬筆，這是不可能的事(正常值應該是幾十筆)。我看了一下比對的語法沒錯，因此當下就執行一下該語法看看兩邊資料表差異筆數，結果又是破萬筆，這下確認的確有異常。我當下取出兩邊的同一筆資料看看差異性為何(如下圖所示)，我發現兩邊的時間欄位格式不同。但informix的DBA表示沒改過Schema，因此我當下解法是先將SQL這邊的格式改為DataTime2(7)讓兩邊一致就OK了。
由於大家都說以前跑很正常也沒改過Schema，為何今年就不正常呢 ? 因此我去看一下作業LOG，發現其實從4個月前的某一天就開始不正常了，當下翻了一下日誌看看當天我有做了甚麼事嗎。一看發現不得了，那天就是我將資料庫相容性層級由90調成100的日子。會是相容性層級造成的嗎 ? 於是我就LAB一下寫了這一篇筆記一下。
首先我先在DB1這一個資料庫(相容性層級為110)建立一個tbDateTime_CL110的資料表，表內只有一個時間欄位chgdtime其格式是DateTime2(7)。
如下圖語法所示我再建一個資料庫叫TestDB將其相容性層級改為100後利用Select Into的方式建一張tbDateTime_CL100資料表，然後再將TestDB相容性層級改為90後再建一張tbDateTime_CL90的資料表。兩張資料表來源都是從剛剛的DB1資料庫tbDateTime_CL110資料表來的，兩張表筆數都是19051筆。
接下來我們看看剛剛產生的兩張表中的時間欄位有不同嗎 ? 如下圖所示MSSQL皆利用datetime2(7)來做為該資料表欄位的格式，並無不同。
由於我發生問題的情境是一端用ODBC建LinkedServer的informix，而另一端是MSSQL。因此這種模式的測試才是重頭戲。如下圖所示，有一LinkedServer名字為INFORMIX64。
我們用一模一樣的方式在TestDB相容性層級為90及100的狀況下各建一張資料表叫tbDateTime_ODBC_CL90 及 tbDateTime_ODBC_CL100，兩張表的資料來源都是從informix透過LinkedServer方式傳過來的，資料筆數一樣是19051筆。
完成上一步驟後我們再來檢視這兩張資料表tbDateTime_ODBC_CL90 及 tbDateTime_ODBC_CL100。如下圖，你會發現TestDB在相容性層級為90下產出的資料表tbDateTime_ODBC_CL90，其chgdtime居然是datetime而非datetime2(7)。
經過上述實驗後可以確認問題就是因為我調了相容性層級造成，當原來MSSQL Database相容性層級是90的狀況下，透過ODBC從informix讀入資料時，其來源的時間格式被轉為datetime，因此可以正常比對兩邊資料表資料。而當我將資料庫相容性層級調成100後再讀入的資料，其格式會變成符合來源格式的datetime2(7) (但我MS SQL這一邊的那張表還是datetime型態) 因此就發生了錯誤。我也爬了一下官網中對於相容性層級90跟100的差異處，看的不是很了解，但其中有提到ODBC 函數 {fn CURDATE()}對於datetime處理有差異(不確定是否有關連)，在此我附上連結，請大家自行拜讀囉。
補充 :
後來我又針對兩邊都是MSSQL做了測試，一組採用ODBC Driver去連接MSSQL建Linked Server，另一組就不透過ODBC直接連接。結果如下圖，用ODBC建立的Linked Server會將datetime(7)轉成nvarchar(27)寫入目的Table。而直接連結的MSSQL就一樣寫入datetime(7)並無轉換。
我是ROCK
rockchang@mails.fju.edu.tw
