【茶包射手日記】ODBC Driver 17 for SQL Server 無法讀取 NVARCHAR(MAX)

再踩 SQL 資料庫地雷一枚。

配合停用 TLS 1.0，把一些老舊程式用的 ODBC 版本升級到 ODBC 17 (參考：ODBC 與 OLEDB 之 SQL Server TLS 1.2 支援問題)，遇到詭異現象：升級後有個 SELECT ColKey, ColContent FROM ... 查詢傳回的 ColKey 內容正常， ColContent 則是空字串。換版前並無此問題，同一程式在另一台測試環境也正常。

經過比對測試，發現關鍵在於 ColContent 欄位型別是 NVARCHAR(MAX)，用 SELECT CONVERT(NVARCHAR(MAX), 'TEST') 也能重現問題。 至於可正常執行的測試環境，與問題環境差在 DSN 用的驅動程式是 SQL Server 10.0 Client，而非 ODBC Driver 17 for SQL Server。 由此推論：某些版本 ODBC SQL 驅動程式不支援 NVARCHAR(MAX)。在測試機器找到四種 SQL 驅動程式：ODBC Driver 13 for SQL Server、ODBC Driver 17 for SQL Server、SQL Server 10.0、SQL Server Native Client 11.0，

為四種驅動程式各自建立 DSN：

使用以下程式驗證，若支援 NVARCHAR(MAX) 應該出現 123,ABC，否則只會看到 123，：

sqlCmd = "SELECT CONVERT(NVARCHAR(4000),N'123'),CONVERT(NVARCHAR(MAX), N'ABC')"

cn.open "DSN=ODBC17"
rs.open sqlCmd, cn, 0, 1, 1
Response.Write "<li> ODBC 17 Test="
Response.Write rs(0) & ","
Response.Write rs(1)
rs.Close
cn.Close

cn.open "DSN=ODBC13"
rs.open sqlCmd, cn, 0, 1, 1
Response.Write "<li> ODBC 13 Test="
Response.Write rs(0) & ","
Response.Write rs(1)
rs.Close
cn.Close

cn.Open "DSN=SQL11"
rs.Open sqlCmd, cn, 0, 1, 1
Response.Write "<li> SQL Naitve Client 11 Test="
Response.Write rs(0) & ","
Response.Write rs(1)
rs.close
cn.close

cn.Open "DSN=SQL10"
rs.Open sqlCmd, cn, 0, 1, 1
Response.Write "<li> SQL 10 Test="
Response.Write rs(0) & ","
Response.Write rs(1)
rs.close
cn.close

測試結果令人意外，除了較古老的 SQL Server Client 10.0，其餘全軍覆沒：

爬文查到幾篇相關文獻：

• Selecting VARCHAR(max) or NVARCHAR(max) columns using "ODBC Driver 11 for SQL Server" results in blank values or random memory garbage
  使用 ODBC Driver 11 for SQL Server 讀取 NVARCHAR(MAX) 傳回空白或垃圾資料，建議解法是改用 MSOLEDBSQL。另一篇回應案例原本用 SQL Server driver 6.0 (跟問題環境升級前的版本相同)，升級成 ODBC Driver 13 for SQL Server 出錯，一樣是舊版 OK，新版反而不行。
• VB6 換 ODBC 13 連 Azure SQL VARCHAR(MAX) 傳回亂碼資料
• SQL Native Client VARCHAR(MAX) 傳回 null，換 SQL OLEDB 解決

歸納結論：ODBC Driver 11 for SQL Server、ODBC Driver 13 for SQL Server、ODBC Driver 17 for SQL Server、SQL Native Client 都有查詢 VARCHAR(MAX) 傳回空白或亂碼的前科，一般建議換成 Microsoft OLE DB Driver for SQL Serve，而我測試 SQL Server 10.0 也沒問題。若無法換驅動程式，將 NVARCHAR(MAX) 轉型成 NVARCHAR(4000) 或 NTEXT 亦是 Workaround。

Case of selecting NVARCHAR(MAX) returns null with ODBC driver for SQL server.