Msg 6355 "Conversion of one or more characters from XML to target collation impossible" when querying sys.dm_exec_query_plan

I like to find missing indexes on the go, looking at the execution plans!

It can potentially give me an indication where further to look at if I want to improve something that is currently running.

For doing this I use the following query:

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%'

It generally works fine; however, I have recently encountered a problem with collation and XML:

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


I have already found out what is causing it:

-- 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

My question is:

What inside my query gets

The problem is that you are converting XML data, which is stored as Unicode (UTF-16 specifically), into 8-bit data. 8-bit data requires a code page to know which set of up to 256 characters to use (or up to approximately 24k characters if using a Double-Byte Character Set, but the vast majority of the time it's 256 characters max). The issue here is that there is a character (or possibly several) in the XML data that either a) does not exist in the code page specified by the default collation of the current database, or b) does not exist in any code page.

For example, the following queries show a character that does not exist in most code pages (or maybe none) and so gets the same error when converting to VARCHAR, but converting to NVARCHAR works:
SELECT NCHAR(0x1234);
-- ሴ

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

SELECT CONVERT(NVARCHAR(MAX), @Test);
-- ሴ

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
*/


Please convert to NVARCHAR(MAX) instead of VARCHAR(MAX). Your WHERE predicate should be:
CONVERT(NVARCHAR(MAX), qp.query_plan) LIKE N'%

(please note the addition of the "N" prefixing the string literal — not exactly necessary, but a good habit to get into).

Converting XML data to VARCHAR is only truly safe if the query is executing in a database that has a UTF-8 collation as its default collation, and that is only available starting in SQL Server 2019.

Finally, to answer your question of "would forcing a collation via COLLATE help?": No, not when converting from XML. You can't add the COLLATE clause to the XML data as collation does not apply to XML (you would get "Expression type xml is invalid for COLLATE clause."). And, placing the COLLATE outside of the CONVERT() doesn't help as that is applied after the conversion, which here fails. You also cannot place the COLLATE just after the VARCHAR(MAX)` as that is a datatype and not data (you would get "Incorrect syntax near the keyword 'COLLATE'.").

For more info on working with collations / encodings / Unicode, please visit my site: Collations Info

StackExchange Database Administrators Q#291505, answer score: 10