查詢 sys.dm_exec_query_plan 時，消息 6355“不可能將一個或多個字元從 XML 轉換為目標排序規則”

我喜歡隨時隨地查找缺失的索引，查看執行計劃！

如果我想改進目前正在執行的東西，它可能會給我一個指示，進一步研究的地方。

為此，我使用以下查詢：

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


SELECT 
   er.session_id,
   er.blocking_session_id,
   er.start_time,
   er.status,
   dbName = DB_NAME(er.database_id),
   er.wait_type,
   er.wait_time,
   er.last_wait_type,
   er.granted_query_memory,
   er.reads,
   er.logical_reads,
   er.writes,
   er.row_count,
   er.total_elapsed_time,
   er.cpu_time,
   er.open_transaction_count,
   er.open_transaction_count,
   s.text,
   qp.query_plan,
   logDate = CONVERT(DATETIME,GETDATE()),
   logTime = CONVERT(DATETIME,GETDATE())
FROM sys.dm_exec_requests er 
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) s
CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) qp
WHERE 
   CONVERT(VARCHAR(MAX), qp.query_plan) LIKE '%<missing%'

它通常工作正常；但是，我最近遇到了排序規則和 XML 的問題：

錯誤資訊說：

Msg 6355, Level 16, State 1, Line 40
Conversion of one or more characters from XML to target collation impossible

我已經發現是什麼原因造成的：

-- get only the applications from Italy:
exec usp_sel_outstandingItems            
                                   @startdate='2021-04-07 00:00:00',           
                                   @endDate='2021-08-15 00:00:00',           
                                   @statusDateStart=NULL,           
                                   @statusDateEnd=NULL,           
                                   @office=N'UK',           
                                   @country=N'IT ',           
                                   @userState=N'ParticipantPlaced',           
                                   @outstandingBalance=0

我的問題是：

我的查詢中有什麼對排序規則感到不安？

我能做些什麼來克服這個錯誤？

整理提示？

select @@version

Microsoft SQL Server 2016 (SP2-CU15-GDR) 
(KB4583461) - 13.0.5865.1 (X64)   Oct 31 2020 02:43:57   
Copyright (c) Microsoft Corporation  Enterprise Edition: 
Core-based Licensing (64-bit) 
on Windows Server 2012 R2 Datacenter 6.3 <X64> (Build 9600: ) 
(Hypervisor) 

問題是您正在將XML儲存為 Unicode（特別是 UTF-16）的數據轉換為 8 位數據。8 位數據需要程式碼頁才能知道要使用哪一組最多 256 個字元（如果使用雙字節字元集，則最多大約 24k 個字元，但絕大多數情況下最多為 256 個字元）。這裡的問題是 XML 數據中有一個（或可能有幾個）字元，要麼 a) 目前數據庫的預設排序規則指定的程式碼頁中不存在，要麼 b) 任何程式碼頁中都不存在。

例如，以下查詢顯示了大多數程式碼頁中不存在的字元（或者可能不存在），因此在轉換為 時會出現相同的錯誤VARCHAR，但轉換為NVARCHAR有效：

SELECT NCHAR(0x1234);
-- ሴ

DECLARE @Test XML;
SET @Test = CONVERT(XML, N'<a>' + NCHAR(0x1234) + N'</a>')
SELECT @Test;
-- <a>ሴ</a>

SELECT CONVERT(NVARCHAR(MAX), @Test);
-- <a>ሴ</a>

SELECT CONVERT(VARCHAR(MAX), @Test);
/*
Msg 6355, Level 16, State 1, Line XXXXX
Conversion of one or more characters from XML to target collation impossible
*/

請轉換為NVARCHAR(MAX)而不是VARCHAR(MAX). 你的WHERE謂詞應該是：

CONVERT(NVARCHAR(MAX), qp.query_plan) LIKE N'%<missing%'

（請注意在字元串文字前添加“N”前綴——不是完全必要的，但要養成的好習慣）。

僅當查詢在以 UTF-8 排序規則作為其預設排序規則的數據庫中執行且僅在 SQL Server 2019 中才可用時，將XML數據轉換為真正安全。VARCHAR

最後，回答您“會通過COLLATE幫助強制排序嗎？”的問題：不，不是從XML. 您不能將COLLATE子句添加到XML數據中，因為排序規則不適用於XML（您會得到“表達式類型 xml 對 COLLATE 子句無效。”）。並且，放置COLLATE外部CONVERT()並沒有幫助，因為轉換後應用了它，這裡失敗了。您也不能將COLLATE緊隨其後的VARCHAR(MAX)作為數據類型而不是數據（您會得到“關鍵字’COLLATE’附近的語法不正確。”）。

---

有關使用排序規則/編碼/Unicode 的更多資訊，請訪問我的網站：排序規則資訊

引用自：https://dba.stackexchange.com/questions/291505