none
MAP 9.5 - SQL Server Database Details Report missing data in SQL Server Database Summary Sheet RRS feed

  • Question

  • Using 9.5 version.

    The database summary sheet does not have data only showing:

    column1       column2

    data A1         data B1

    data A2         dataB2

    I looked at the log and it has this entry showing an error which I think is the issue, but don't know how to fix it:

    <2017-01-23 15:39:57.50 Generate reports@DocumentGenerator,I>  GenerateDocument: SqlServerDatabaseDetails-01-23-2017-15h39m57s
    <2017-01-23 15:39:58.99 Generate reports@CommonFunctions,I> GetConfigurationSetting() - Overriding default value '200000' with value from .config file of '200000' for key 'MaxRowsAllowedPerTableInExcel' in class 'Microsoft.AssessmentPlatform.DocumentGenerationEngine.ExcelEngine'
    <2017-01-23 15:40:07.38 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.
       |   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

    I uninstall MAP along with the locaDB and reinstalled and still have the same error.

    thanks,

    Victor

    Tuesday, January 24, 2017 5:34 PM

All replies

  • Did you ever find an answer to this as facing the same issue, also MAPS doesn't collect user information?
    Wednesday, June 5, 2019 8:10 AM
  • Hello! I just encountered and fixed this bug in the MAP Toolkit. I verified the bug exists 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. The bug is in a stored procedure that is present in any database created by the MAP Toolkit (File | Create/Select Database...). As stated, the generated spreadsheet "DatabaseSummary" tab has no data in it, just the placeholder rows and columns. The bug also of course occurs if you invoke the stored procedure directly.

    To fix the bug I developed code that patches the generated database.  While it may be possible to patch the "model database" which the MAP Toolkit uses internally when the user creates a new database, I did not try to do that. Another option you have is to discover which individual record(s) are causing the problem, and either hand-edit those records, hand-delete those records, or exclude those servers from your inventory. I didn't like that choice either.

    Here is the code I use to patch each inventory database created by the MAP Toolkit. Note that this code also fixes another bug present in v9.8.8.0 MAP Toolkit, an exception that gets thrown for names that include certain characters that must be escaped in XML. ("Mod #1" fixes the XML bug, "Mod #2" fixed the overflow bug.)

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

    For more information you may want to refer to the URL mentioned in the second line of the script above.

    I hope this helps!

    Bradley

    • Proposed as answer by Brad411 Friday, August 2, 2019 4:35 PM
    Friday, August 2, 2019 4:30 PM