All we need is an easy explanation of the problem, so here it is.
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 upset about the collation?
What can I do to possibly get over this error?
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)
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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
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 */
Please convert to
NVARCHAR(MAX) instead of
WHERE predicate should be:
CONVERT(NVARCHAR(MAX), qp.query_plan) LIKE N'%<missing%'
(please note the addition of the "N" prefixing the string literal — not exactly necessary, but a good habit to get into).
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
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂