none
SqlServerDatabaseDetails spreadsheet DatabaseSummary tab is malformed - error in MapToolkit.log indicates only illegal xml character - how to work around this? RRS feed

  • Question

  • Hello, I am hoping someone can help me work around an issue that is coming up with MAP Toolkit. I am using version 9.7.1.0.

    The tool works fine on a small sample of computers, but when I expand the inventory to look in one of our OUs (~6000 machines), the "SqlServerDatabaseDetails" spreadsheet generated ends up with a malformed / unformed "DatabaseSummary" tab: it contains only some "template" kind of info (Column1, Column2, data A1 data B1, data A2, data B2, data A3, data B3).

    Looking in the MapToolkit.log file, for the time at which I requested the spreadsheet, I find the following two lines:

    <2018-02-13 10:47:14.07 Generate reports@DocumentGenerator,I> GenerateDocument: SqlServerDatabaseDetails-02-13-2018-10h47m14s <2018-02-13 10:47:21.81 Generate reports@Workbookgen,E> UpdateWorksheet: Failed ProcessingInstruction 'Table(Table)' with Exception 'System.Data.SqlClient.SqlException (0x80131904): XML parsing: line 15, character 31, illegal xml character

    I am guessing that the data associated with a database has some unexpected and un-escaped character in it. But I have no idea how to locate the problem.

    questions:

    Is there a way to get more detailed error information? (Ideally, to know what value is causing the exception)

    How would I work around the problem once I figured that out? Manually changing the MAP database using an "update" statement?

    Is this a known issue? Is there a short list of places to look or values to change?

    Thank you for your time!

    Bradley

    Wednesday, February 14, 2018 11:58 PM

Answers

  • Together with a Microsoft engineer we found the problem and I learned a little about how to find similar problems in the future.  The bug is in a MAP Toolkit generated stored procedure (stored procedure in the database created to capture inventory).

     

    The problem apparently only occurs if the data contains unprintable ascii control characters (below ascii code 32) (CR LF TAB are acceptable of course). For example, we had a database with a FILE GROUP that included ascii code 31 in the name (somehow!).

     

    An obvious work-around is to change the database property that contains the offensive character, either for real or in the MAP Toolkit database prior to generation of the excel spreadsheet.  But of course one has to be able to find which fields and which values are causing the problem.

     

    The basic form of the bug is the MAP Toolkit’s use of the following construct:

    convert(xml, (... for xml path('')), 1)

     

    While the use of for xml path('') correctly escapes ascii control codes (ascii code 31 is transformed to “&#x1F;”), the convert() function chokes on that string – it throws “illegal xml character” in reference to the & in the _escape sequence_!

     

    Here’s a simplified example:

    set nocount on;
    declare @s varchar(max) = 'EDW' + CHAR(31) + '_Data';
    
    -- works fine:
    select @s as blah for xml path('');
    
    -- this works -- if we escape the ampersand, then no "illegal xml character" exception is thrown
    select convert(xml, replace((select @s as blah for xml path('')), '&', '&amp;'), 1) as [escaping the ampersand is perhaps what we want];
    
    -- but the stored procedure uses convert(xml, ..., 1) and the 2nd argument is perhaps implicitly converted to string and then to xml
    -- but as a string the escaped xml contains illegal xml character '&'
    -- CONVERT cannot handle it
    select convert(xml, (select @s as blah for xml path('')), 1) as [This is what is done in stored procedure [SqlServer_Reporting]].[GetSqlServerDetailsDatabaseSummary]]];


    which produces this output:

    XML_F52E2B61-18A1-11d1-B105-00805F49916B
    ----------------------------------------------------------------
    <blah>EDW&#x1F;_Data</blah>
    
    escaping the ampersand is perhaps what we want
    ----------------------------------------------------------------
    <blah>EDW&amp;#x1F;_Data</blah>
    
    This is what is done in stored procedure [SqlServer_Reporting].[GetSqlServerDetailsDatabaseSummary]
    ----------------------------------------------------------------
    Msg 9420, Level 16, State 1, Line 15
    XML parsing: line 1, character 15, illegal xml character




    To fix the stored procedure, we can “escape” the & so that convert() will not complain:

    Change this

    convert(xml, (... for xml path('')), 1)

    to this

    convert(xml, replace((... for xml path('')), '&', '&amp;'), 1)

     

    Doing this means that the excel spreadsheet will display unprintable codes in their escaped format, which is nice.

     

    You can programmatically fix the stored procedure using the following code.

    Just put in the name of the MAP database after it is created.

    declare @mapdb sysname = 'SQL-2018-02-26';
    
    -- get proc definition
    declare @targetProc nvarchar(max) = '[SqlServer_Reporting].[GetSqlServerDetailsDatabaseSummary]';
    declare @backupName nvarchar(max) = left(@targetProc, len(@targetProc)-1) + '_orig]';
    set nocount on;
    declare @sql nvarchar(max) = '
        use ' + quotename(@mapdb) + ';
    	select @procdef = object_definition(object_id(''' + @targetProc + '''));
    ';
    declare @procDefn nvarchar(max);
    exec sp_executesql @sql, N'@procdef nvarchar(max) OUTPUT', @procdef = @procDefn OUTPUT;
    
    -- create a copy of the broken stored procedure, if it does not already exist
    declare @backupDefn nvarchar(max) = replace(@procDefn, @targetProc, @backupName);
    declare @outersql nvarchar(max) = '
        use ' + quotename(@mapdb) + ';
        if object_id(''' + @backupName + ''') is null
        begin
            exec (''' + replace(@backupDefn, '''', '''''') + ''');
            print ''backup copy made'';
        end
        else
        begin
            print ''Creation of backup copy not needed:'';
            print ''Procedure "' + @backupName + '" already exists.'';
        end
    ';
    EXEC (@outersql);
    
    -- modify the proc to wrap 2nd argument of "convert(xml, ..., 1)" in a replace() function to escape &
    -- where the 2nd argument is of the form "(select ... for xml path(''))".
    print '';
    declare @convertIntro nvarchar(max) = 'CONVERT(XML, ';
    declare @i int = charindex(@convertIntro + '(SELECT ', @procDefn);
    
    declare @forXmlIntro nvarchar(max) = 'FOR XML PATH ('''') )';
    declare @j int = charindex(@forXmlIntro + ', 1)', @procDefn, @i+1);
    if @i = 0 or @j = 0
    begin
        print 'Unexpected problem: could not find code to wrap. Procedure was not changed.';
        print 'Perhaps the bug has been fixed in the current version of MAP Toolkit.';
        print 'Or perhaps this code has already been run against it.';
    end
    else
    begin
        declare @secondArgStart int = @i + len(@convertIntro);
        declare @secondArgEnd int = @j + len(@forXmlIntro) - 1; 
        declare @secondArgument nvarchar(max) = substring(@procDefn, @secondArgStart, @secondArgEnd - @secondArgStart + 1);
        declare @newDefn nvarchar(max) =
            left(@procDefn, @secondArgStart)
            + 'replace( ' + @secondArgument + ', ''&'', ''&amp;'')'
            + substring(@procDefn, @secondArgEnd+1, 9999)
        ;
        set @newDefn = replace(@newDefn, 'CREATE PROCEDURE', 'ALTER PROCEDURE');
        set @outersql = '
            use ' + quotename(@mapdb) +';
            exec (''' + replace(@newDefn, '''', '''''') + ''')
        ';
        EXEC (@outersql);
        print 'Procedure ' + @targetProc + ' has been modified successfully.';
    end
    


    (thx to Moises Gabriel for the framework for the above code, at https://social.technet.microsoft.com/Forums/en-US/1efb0f22-5173-4023-b012-93e8b28d0b30/assessment-failing-after-inventory?forum=map)


    For the record, the following steps were used to find the specific bug in the MAP Toolkit:

    • The excel spreadsheet SQL Server Database Details, "DatabaseSummary" tab is the problem.
    • Looking at C:\Program Files\Microsoft Assessment and Planning Toolkit\XMLLibrary folder, at the file "SqlServerDatabaseDetails.xml", we can see that this tab is generated by stored procedure "[SqlServer_Reporting].[GetSqlServerDetailsDatabaseSummary]".
    • That stored procedure is found in the _generated_ database (not the "MAP_ConfigInfo" nor the "SystemInfo" databases).
    • That stored procedure is not that long, and contains only one reference to xml.
    • Looking at the "select … for xml" line, there are only two string fields (the rest are numeric), allowing me to correctly guess that one or both of these fields could not be converted.  
    • A simple query then reproduced the "illegal xml character" error.
    • A slightly more complex query allowed me to find the exact value causing the problem.
    • Analyzing the value revealed the unprintable ascii code 31, which was of course not obvious from just looking at the displayed value.
    • Experimenting with convert() and "select … for xml" revealed the problem.


    Perhaps you would prefer to avoid changing the MAP Toolkit stored procedure. In this case you can use the code below to find the values that are causing the problem. When the problem occurs with the above scenario (DatabaseSummary tab of excel spreadsheet SqlServerDatabaseDetails), I believe the problem will be a character in either the FILE NAME or FILE GROUP of a database in the inventory. You can cannect to your MAPS database and find the "bad" values using the code below.

    select 
    	ServerName, 
    	DbName,
    	case
    		when ([Filegroup] is not null and TRY_CONVERT(xml, [FileGroup], 1) is null) then 'Filegroup - invalid character'
    		when ([Filename] is not null and TRY_CONVERT(xml, [Filename], 1) is null) then 'Filename - invalid character'
    		else 'Unexpected problem'
    	end as Problem,
    	[Filegroup],
    	(select [Filegroup] for xml path('')) as FilegroupXml,
    	[Filename],
    	(select [Filename] for xml path('')) as FilenameXml
    from 
    	[SqlServer_Inventory].[DataBaseFileGroup]
    where
    	([Filegroup] is not null and TRY_CONVERT(xml, [FileGroup], 1) is null)
    	or ([Filename] is not null and TRY_CONVERT(xml, [Filename], 1) is null)
    order by 
    	ServerName,
    	DbName,
    	[Filegroup],
    	[filename]
    ;
    

    The above SQL produced output similar to the following:

    Server    DbName   Problem                                       Filegroup           FilegroupXml                                                        Filename                                                                                                     FilenameXml
    S1               D1       Filegroup - invalid character        EDW_Data        <Filegroup>EDW&#x1F;_Data</Filegroup>        Q:\MSSQL11.MSSQLSERVER\MSSQL\DATA\obscured_filename02.ndf         <Filename>Q:\MSSQL11.MSSQLSERVER\MSSQL\DATA\obscured_filename02.ndf</Filename>
    S2               D2       Filegroup - invalid character        EDW_Data        <Filegroup>EDW&#x1F;_Data</Filegroup>        Q:\MSSQL11.MSSQLSERVER\MSSQL\DATA\obscured_filename03.ndf         <Filename>Q:\MSSQL11.MSSQLSERVER\MSSQL\DATA\obscured_filename03.ndf</Filename>
    S3               D3       Filegroup - invalid character        EDW_Data        <Filegroup>EDW&#x1F;_Data</Filegroup>        Q:\MSSQL11.MSSQLSERVER6\MSSQL\DATA\obscured_filename02.ndf        <Filename>Q:\MSSQL11.MSSQLSERVER6\MSSQL\DATA\obscured_filename02.ndf</Filename>
    S4               D4       Filegroup - invalid character        EDW_Data        <Filegroup>EDW&#x1F;_Data</Filegroup>        Q:\MSSQL11.MSSQLSERVER6\MSSQL\DATA\obscured_filename03.ndf        <Filename>Q:\MSSQL11.MSSQLSERVER6\MSSQL\DATA\obscured_filename03.ndf</Filename>
    S5               D5       Filegroup - invalid character        EDW_Data        <Filegroup>EDW&#x1F;_Data</Filegroup>        Q:\MSSQL11.MSSQLSERVER6\MSSQL\DATA\obscured_filename02.ndf        <Filename>Q:\MSSQL11.MSSQLSERVER6\MSSQL\DATA\obscured_filename02.ndf</Filename>
    S6               D6       Filegroup - invalid character        EDW_Data        <Filegroup>EDW&#x1F;_Data</Filegroup>        Q:\MSSQL11.MSSQLSERVER6\MSSQL\DATA\obscured_filename03.ndf        <Filename>Q:\MSSQL11.MSSQLSERVER6\MSSQL\DATA\obscured_filename03.ndf</Filename>
    

    Bradley



    • Edited by Brad411 Thursday, March 1, 2018 7:16 PM added sql output
    • Marked as answer by Brad411 Thursday, March 1, 2018 7:16 PM
    Thursday, March 1, 2018 6:56 PM

All replies

  • Hi Brad,

    You are right . May be data in table contains reserved character which is causing the issue. It is a well known error of XML parsing.

    You can connect to Database directly using SSMS and validate all Views related to SQLServer. This thread may help you in connecting to database- https://social.technet.microsoft.com/Forums/en-US/9b1a61d6-568b-4114-9eee-f380185e175d/cannot-see-the-database-using-by-map-toolkit?forum=map

    You can also send an email to support Team at mapfdbk@microsoft.com with your database backup and Team will help in resolving the issue.

    Tuesday, February 27, 2018 11:42 AM
  • Together with a Microsoft engineer we found the problem and I learned a little about how to find similar problems in the future.  The bug is in a MAP Toolkit generated stored procedure (stored procedure in the database created to capture inventory).

     

    The problem apparently only occurs if the data contains unprintable ascii control characters (below ascii code 32) (CR LF TAB are acceptable of course). For example, we had a database with a FILE GROUP that included ascii code 31 in the name (somehow!).

     

    An obvious work-around is to change the database property that contains the offensive character, either for real or in the MAP Toolkit database prior to generation of the excel spreadsheet.  But of course one has to be able to find which fields and which values are causing the problem.

     

    The basic form of the bug is the MAP Toolkit’s use of the following construct:

    convert(xml, (... for xml path('')), 1)

     

    While the use of for xml path('') correctly escapes ascii control codes (ascii code 31 is transformed to “&#x1F;”), the convert() function chokes on that string – it throws “illegal xml character” in reference to the & in the _escape sequence_!

     

    Here’s a simplified example:

    set nocount on;
    declare @s varchar(max) = 'EDW' + CHAR(31) + '_Data';
    
    -- works fine:
    select @s as blah for xml path('');
    
    -- this works -- if we escape the ampersand, then no "illegal xml character" exception is thrown
    select convert(xml, replace((select @s as blah for xml path('')), '&', '&amp;'), 1) as [escaping the ampersand is perhaps what we want];
    
    -- but the stored procedure uses convert(xml, ..., 1) and the 2nd argument is perhaps implicitly converted to string and then to xml
    -- but as a string the escaped xml contains illegal xml character '&'
    -- CONVERT cannot handle it
    select convert(xml, (select @s as blah for xml path('')), 1) as [This is what is done in stored procedure [SqlServer_Reporting]].[GetSqlServerDetailsDatabaseSummary]]];


    which produces this output:

    XML_F52E2B61-18A1-11d1-B105-00805F49916B
    ----------------------------------------------------------------
    <blah>EDW&#x1F;_Data</blah>
    
    escaping the ampersand is perhaps what we want
    ----------------------------------------------------------------
    <blah>EDW&amp;#x1F;_Data</blah>
    
    This is what is done in stored procedure [SqlServer_Reporting].[GetSqlServerDetailsDatabaseSummary]
    ----------------------------------------------------------------
    Msg 9420, Level 16, State 1, Line 15
    XML parsing: line 1, character 15, illegal xml character




    To fix the stored procedure, we can “escape” the & so that convert() will not complain:

    Change this

    convert(xml, (... for xml path('')), 1)

    to this

    convert(xml, replace((... for xml path('')), '&', '&amp;'), 1)

     

    Doing this means that the excel spreadsheet will display unprintable codes in their escaped format, which is nice.

     

    You can programmatically fix the stored procedure using the following code.

    Just put in the name of the MAP database after it is created.

    declare @mapdb sysname = 'SQL-2018-02-26';
    
    -- get proc definition
    declare @targetProc nvarchar(max) = '[SqlServer_Reporting].[GetSqlServerDetailsDatabaseSummary]';
    declare @backupName nvarchar(max) = left(@targetProc, len(@targetProc)-1) + '_orig]';
    set nocount on;
    declare @sql nvarchar(max) = '
        use ' + quotename(@mapdb) + ';
    	select @procdef = object_definition(object_id(''' + @targetProc + '''));
    ';
    declare @procDefn nvarchar(max);
    exec sp_executesql @sql, N'@procdef nvarchar(max) OUTPUT', @procdef = @procDefn OUTPUT;
    
    -- create a copy of the broken stored procedure, if it does not already exist
    declare @backupDefn nvarchar(max) = replace(@procDefn, @targetProc, @backupName);
    declare @outersql nvarchar(max) = '
        use ' + quotename(@mapdb) + ';
        if object_id(''' + @backupName + ''') is null
        begin
            exec (''' + replace(@backupDefn, '''', '''''') + ''');
            print ''backup copy made'';
        end
        else
        begin
            print ''Creation of backup copy not needed:'';
            print ''Procedure "' + @backupName + '" already exists.'';
        end
    ';
    EXEC (@outersql);
    
    -- modify the proc to wrap 2nd argument of "convert(xml, ..., 1)" in a replace() function to escape &
    -- where the 2nd argument is of the form "(select ... for xml path(''))".
    print '';
    declare @convertIntro nvarchar(max) = 'CONVERT(XML, ';
    declare @i int = charindex(@convertIntro + '(SELECT ', @procDefn);
    
    declare @forXmlIntro nvarchar(max) = 'FOR XML PATH ('''') )';
    declare @j int = charindex(@forXmlIntro + ', 1)', @procDefn, @i+1);
    if @i = 0 or @j = 0
    begin
        print 'Unexpected problem: could not find code to wrap. Procedure was not changed.';
        print 'Perhaps the bug has been fixed in the current version of MAP Toolkit.';
        print 'Or perhaps this code has already been run against it.';
    end
    else
    begin
        declare @secondArgStart int = @i + len(@convertIntro);
        declare @secondArgEnd int = @j + len(@forXmlIntro) - 1; 
        declare @secondArgument nvarchar(max) = substring(@procDefn, @secondArgStart, @secondArgEnd - @secondArgStart + 1);
        declare @newDefn nvarchar(max) =
            left(@procDefn, @secondArgStart)
            + 'replace( ' + @secondArgument + ', ''&'', ''&amp;'')'
            + substring(@procDefn, @secondArgEnd+1, 9999)
        ;
        set @newDefn = replace(@newDefn, 'CREATE PROCEDURE', 'ALTER PROCEDURE');
        set @outersql = '
            use ' + quotename(@mapdb) +';
            exec (''' + replace(@newDefn, '''', '''''') + ''')
        ';
        EXEC (@outersql);
        print 'Procedure ' + @targetProc + ' has been modified successfully.';
    end
    


    (thx to Moises Gabriel for the framework for the above code, at https://social.technet.microsoft.com/Forums/en-US/1efb0f22-5173-4023-b012-93e8b28d0b30/assessment-failing-after-inventory?forum=map)


    For the record, the following steps were used to find the specific bug in the MAP Toolkit:

    • The excel spreadsheet SQL Server Database Details, "DatabaseSummary" tab is the problem.
    • Looking at C:\Program Files\Microsoft Assessment and Planning Toolkit\XMLLibrary folder, at the file "SqlServerDatabaseDetails.xml", we can see that this tab is generated by stored procedure "[SqlServer_Reporting].[GetSqlServerDetailsDatabaseSummary]".
    • That stored procedure is found in the _generated_ database (not the "MAP_ConfigInfo" nor the "SystemInfo" databases).
    • That stored procedure is not that long, and contains only one reference to xml.
    • Looking at the "select … for xml" line, there are only two string fields (the rest are numeric), allowing me to correctly guess that one or both of these fields could not be converted.  
    • A simple query then reproduced the "illegal xml character" error.
    • A slightly more complex query allowed me to find the exact value causing the problem.
    • Analyzing the value revealed the unprintable ascii code 31, which was of course not obvious from just looking at the displayed value.
    • Experimenting with convert() and "select … for xml" revealed the problem.


    Perhaps you would prefer to avoid changing the MAP Toolkit stored procedure. In this case you can use the code below to find the values that are causing the problem. When the problem occurs with the above scenario (DatabaseSummary tab of excel spreadsheet SqlServerDatabaseDetails), I believe the problem will be a character in either the FILE NAME or FILE GROUP of a database in the inventory. You can cannect to your MAPS database and find the "bad" values using the code below.

    select 
    	ServerName, 
    	DbName,
    	case
    		when ([Filegroup] is not null and TRY_CONVERT(xml, [FileGroup], 1) is null) then 'Filegroup - invalid character'
    		when ([Filename] is not null and TRY_CONVERT(xml, [Filename], 1) is null) then 'Filename - invalid character'
    		else 'Unexpected problem'
    	end as Problem,
    	[Filegroup],
    	(select [Filegroup] for xml path('')) as FilegroupXml,
    	[Filename],
    	(select [Filename] for xml path('')) as FilenameXml
    from 
    	[SqlServer_Inventory].[DataBaseFileGroup]
    where
    	([Filegroup] is not null and TRY_CONVERT(xml, [FileGroup], 1) is null)
    	or ([Filename] is not null and TRY_CONVERT(xml, [Filename], 1) is null)
    order by 
    	ServerName,
    	DbName,
    	[Filegroup],
    	[filename]
    ;
    

    The above SQL produced output similar to the following:

    Server    DbName   Problem                                       Filegroup           FilegroupXml                                                        Filename                                                                                                     FilenameXml
    S1               D1       Filegroup - invalid character        EDW_Data        <Filegroup>EDW&#x1F;_Data</Filegroup>        Q:\MSSQL11.MSSQLSERVER\MSSQL\DATA\obscured_filename02.ndf         <Filename>Q:\MSSQL11.MSSQLSERVER\MSSQL\DATA\obscured_filename02.ndf</Filename>
    S2               D2       Filegroup - invalid character        EDW_Data        <Filegroup>EDW&#x1F;_Data</Filegroup>        Q:\MSSQL11.MSSQLSERVER\MSSQL\DATA\obscured_filename03.ndf         <Filename>Q:\MSSQL11.MSSQLSERVER\MSSQL\DATA\obscured_filename03.ndf</Filename>
    S3               D3       Filegroup - invalid character        EDW_Data        <Filegroup>EDW&#x1F;_Data</Filegroup>        Q:\MSSQL11.MSSQLSERVER6\MSSQL\DATA\obscured_filename02.ndf        <Filename>Q:\MSSQL11.MSSQLSERVER6\MSSQL\DATA\obscured_filename02.ndf</Filename>
    S4               D4       Filegroup - invalid character        EDW_Data        <Filegroup>EDW&#x1F;_Data</Filegroup>        Q:\MSSQL11.MSSQLSERVER6\MSSQL\DATA\obscured_filename03.ndf        <Filename>Q:\MSSQL11.MSSQLSERVER6\MSSQL\DATA\obscured_filename03.ndf</Filename>
    S5               D5       Filegroup - invalid character        EDW_Data        <Filegroup>EDW&#x1F;_Data</Filegroup>        Q:\MSSQL11.MSSQLSERVER6\MSSQL\DATA\obscured_filename02.ndf        <Filename>Q:\MSSQL11.MSSQLSERVER6\MSSQL\DATA\obscured_filename02.ndf</Filename>
    S6               D6       Filegroup - invalid character        EDW_Data        <Filegroup>EDW&#x1F;_Data</Filegroup>        Q:\MSSQL11.MSSQLSERVER6\MSSQL\DATA\obscured_filename03.ndf        <Filename>Q:\MSSQL11.MSSQLSERVER6\MSSQL\DATA\obscured_filename03.ndf</Filename>
    

    Bradley



    • Edited by Brad411 Thursday, March 1, 2018 7:16 PM added sql output
    • Marked as answer by Brad411 Thursday, March 1, 2018 7:16 PM
    Thursday, March 1, 2018 6:56 PM
  • Update! I encountered another bug in MAP Toolkit - verified present in version 9.9.13.0 as well as 9.8.8.0.

    The bug occurs if a database has files or logs with sizes in the terabyte range. It is an "arithmetic overflow" due to an attempt to cast these fields from BIGINT to MONEY. This error in the stored procedure results in the tell-tale failure of the generated excel spreadsheet "DatabaseSummary" tab to contain anything except a placeholder showing only

    column1       column2

    data A1         data B1

    data A2         dataB2

    (This behavior is the same as above, for the XML issue.)

    The Map Toolkit logs (at %appdata%\..\local\Microsoft\MAP\) show the "arithmetic overflow" exception

    <2019-08-01 14:10:04.54 Generate reports@Workbookgen,E> UpdateWorksheet: Failed ProcessingInstruction 'Table(Table)' with Exception 'System.Data.SqlClient.SqlException (0x80131904): Arithmetic overflow error converting expression to data type money.
    

    I changed my patch SQL to fix both bugs in the databases created by the MAP Toolkit:

    -- this script patches a database generated by MAP TOOLKIT (tested with v9.8). For more info see:
    -- https://social.technet.microsoft.com/Forums/en-US/46940bdf-3567-4388-91a0-5aa4fa5d5ef9/sqlserverdatabasedetails-spreadsheet-databasesummary-tab-is-malformed-error-in-maptoolkitlog?forum=map
    
    declare @mapdb sysname = '{target-maptoolkit-database-name}'; -- <<<< put database name here
    
    -- get proc definition
    declare @targetProc nvarchar(max) = '[SqlServer_Reporting].[GetSqlServerDetailsDatabaseSummary]';
    declare @backupName nvarchar(max) = left(@targetProc, len(@targetProc)-1) + '_orig]';
    set nocount on;
    declare @sql nvarchar(max) = '
        use ' + quotename(@mapdb) + ';
    	select @procdef = object_definition(object_id(''' + @targetProc + '''));
    ';
    declare @procDefn nvarchar(max);
    exec sp_executesql @sql, N'@procdef nvarchar(max) OUTPUT', @procdef = @procDefn OUTPUT;
    
    -- create a copy of the broken stored procedure, if it does not already exist
    print 'A. Creating a copy of the broken stored procedure, if it does not already exist:'
    declare @backupDefn nvarchar(max) = replace(@procDefn, @targetProc, @backupName);
    declare @outersql nvarchar(max) = '
        use ' + quotename(@mapdb) + ';
        if object_id(''' + @backupName + ''') is null
        begin
            print ''   - Copying Procedure "' + @targetProc + '".'';
            exec (''' + replace(@backupDefn, '''', '''''') + ''');
            print ''   - Procedure copied to "' + @backupName + '".'';
        end
        else
        begin
            print ''   - Creation of backup copy is not needed:'';
            print ''   - Procedure "' + @backupName + '" already exists.'';
        end
    ';
    EXEC (@outersql);
    
    
    declare @newDefn nvarchar(max) = replace(@procDefn, 'CREATE PROCEDURE', 'ALTER PROCEDURE');
    
    
    -- modify the proc to wrap 2nd argument of "convert(xml, ..., 1)" in a replace() function to escape &
    -- where the 2nd argument is of the form "(select ... for xml path(''))".
    print 'B. Applying mod #1 if problem #1 is present:'
    declare @convertIntro nvarchar(max) = 'CONVERT(XML, ';
    declare @i int = charindex(@convertIntro + '(SELECT ', @newDefn);
    
    declare @forXmlIntro nvarchar(max) = 'FOR XML PATH ('''') )';
    declare @j int = charindex(@forXmlIntro + ', 1)', @newDefn, @i+1);
    if @i = 0 or @j = 0
    begin
        print '   - Mod #1 was not applied because actual code did not match expected code.';
        print '   - Perhaps the newest version of the MAP Toolkit eiminated the problem.';
        print '   - Or perhaps this code patched this database previously.';
        print '   - Worst case: the problem remains but the code is slightly different.';
    end
    else
    begin
        declare @secondArgStart int = @i + len(@convertIntro);
        declare @secondArgEnd int = @j + len(@forXmlIntro) - 1; 
        declare @secondArgument nvarchar(max) = substring(@newDefn, @secondArgStart, @secondArgEnd - @secondArgStart + 1);
        set @newDefn =
            left(@newDefn, @secondArgStart)
            + 'replace( ' + @secondArgument + ', ''&'', ''&amp;'')'
            + substring(@newDefn, @secondArgEnd+1, 9999)
        ;
    
        set @outersql = '
            use ' + quotename(@mapdb) +';
            exec (''' + replace(@newDefn, '''', '''''') + ''')
        ';
        
        EXEC (@outersql);
        print '   - Mod #1 has been applied to Procedure ' + @targetProc + '.';
    end
    
    
    
    -- now we modify the proc to avoid arithmetic overflow in BIGINT fields that are
    -- converted to MONEY. The original authors' intent is not clear; perhaps they
    -- wanted an easy way to output only four decimal places?
    -- We replace MONEY with decimal(23,4) which can handle the largest BIGINT.
    -- We replace the expression convert(MONEY, ...) / 1024
    -- with cast(convert(decimal(23,4), ...) / 1024 as decimal(20,4))
    -- There is an exception for one particular expression
    -- in the form (c1 + c2) / 1024 with c1 and c2 in the form "convert(MONEY, ...)" --
    -- this is converted to cast((c1' + c2') / 1024 as decimal(20,4))
    -- with c1' and c2' in the form "convert(decimal(23,4), ...)"
    print 'C. Applying mod #2 if problem #2 is present:'
    declare @changesMade int = 0;
    declare @count int;
    declare @search1  nvarchar(max) = '+ convert(money';
    declare @replace1 nvarchar(max) = '+ convert(decimal(23,4)';
    declare @expectedCount1 int = 1;
    declare @search2  nvarchar(max) = '(convert(money';
    declare @replace2 nvarchar(max) = 'cast((convert(decimal(23,4)';
    declare @expectedCount2 int = 1;
    declare @search3  nvarchar(max) = 'convert(money';
    declare @replace3 nvarchar(max) = 'cast(convert(decimal(23,4)';
    declare @expectedCount3 int = 3;
    declare @search4  nvarchar(max) = '1024';
    declare @replace4 nvarchar(max) = '1024 as decimal(20,4))';
    declare @expectedCount4 int = 4;
    
    
    -- search and replace 1
    set @count = 
        (len(@newDefn+'x') - len(replace(@newDefn, @search1, '') + 'x')) / (len(@search1 + 'x') - 1);
    if @count = @expectedCount1
    begin
        set @newDefn = replace(@newDefn, @search1, @replace1);
        set @changesMade = @changesMade + @expectedCount1;
        print '   - Found search expression 1 of 4.'
    end
    
    -- search and replace 2
    set @count = 
        (len(@newDefn+'x') - len(replace(@newDefn, @search2, '') + 'x')) / (len(@search2 + 'x') - 1);
    if @count = @expectedCount2
    begin
        set @newDefn = replace(@newDefn, @search2, @replace2);
        set @changesMade = @changesMade + @expectedCount2;
        print '   - Found search expression 2 of 4.'
    end
    
    -- search and replace 3
    set @count = 
        (len(@newDefn+'x') - len(replace(@newDefn, @search3, '') + 'x')) / (len(@search3 + 'x') - 1);
    if @count = @expectedCount3
    begin
        set @newDefn = replace(@newDefn, @search3, @replace3);
        set @changesMade = @changesMade + @expectedCount3;
        print '   - Found search expression 3 of 4.'
    end
    
    -- search and replace 4
    set @count = 
        (len(@newDefn+'x') - len(replace(@newDefn, @search4, '') + 'x')) / (len(@search4 + 'x') - 1);
    if @count = @expectedCount4
    begin
        set @newDefn = replace(@newDefn, @search4, @replace4);
        set @changesMade = @changesMade + @expectedCount4;
        print '   - Found search expression 4 of 4.'
    end
    
    
    if @changesMade != @expectedCount1 + @expectedCount2 + @expectedCount3 + @expectedCount4
    begin
        print '   - Mod #2 was not applied because actual code did not match expected code.';
        print '   - Perhaps the newest version of the MAP Toolkit eiminated the problem.';
        print '   - Or perhaps this code patched this database previously.';
        print '   - Worst case: the problem remains but the code is slightly different.';
    end
    else
    begin
        set @outersql = '
            use ' + quotename(@mapdb) +';
            exec (''' + replace(@newDefn, '''', '''''') + ''')
        ';
        EXEC (@outersql);
        print '   - Mod #2 has been applied to Procedure ' + @targetProc + '.';
    end
    
    

    Friday, August 2, 2019 4:05 PM