looking for some help. I have just started looking at the Blitz stats but getting the following error. This is whilst running sp_BlitzIndex. I am using Version 7.2 (dated 28/01/2019).
FOR XML could not serialize the data for node 'col_definition' because it contains a character (0x001F) which is not allowed in XML. To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive.
Is there any easy way to fix this or is it a case of adding a number of replace statements to remove invalid characters?
It looks like the error is triggering at the start of Step 2 (line 1667). The specific section of code is but I think the following lines would also have the same issue.
RAISERROR (N'Updating #IndexSanity.key_column_names',0,1) WITH NOWAIT;
UPDATE #IndexSanity
SET key_column_names = D1.key_column_names
FROM #IndexSanity si
CROSS APPLY ( SELECT RTRIM(STUFF( (SELECT N', ' + c.column_name
+ N' {' + system_type_name + N' ' + CAST(max_length AS NVARCHAR(50)) + N'}'
AS col_definition
FROM #IndexColumns c
WHERE c.database_id= si.database_id
AND c.schema_name = si.schema_name
AND c.object_id = si.object_id
AND c.index_id = si.index_id
AND c.is_included_column = 0 /*Just Keys*/
AND c.key_ordinal > 0 /*Ignore non-key columns, such as partitioning keys*/
ORDER BY c.object_id, c.index_id, c.key_ordinal
FOR XML PATH('') ,TYPE).value('.', 'nvarchar(max)'), 1, 1, ''))
) D1 ( key_column_names );