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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.