none
process "InventoryResult" Excelsheet fails with arithmetic overflow (MAP 5.0) RRS feed

  • Question

  • The File "InventoryResults-11-20-2009-10h11m55s" looks like the following after I reached a number of 100 inventoried clients. Is this some limitation or a bug in the beta version? This is using Windows 7 with Excel 2007. Also the list in "Inventory Summary Results" -> "All computers" is now empty.


    ------------------------

    For report 'HardwareInventory':

    Excel engine failed while Getting report data.

    Error:



    Arithmetic overflow error converting expression to data type int.


    ------------------------

    BR,
    Torsten.
    Friday, November 20, 2009 9:25 AM

Answers

  • Looks like Moore's law caught up with us; MAP was assuming video adapters would have 2GB or less of RAM.

    To fix this, modify the function dbo.GetHardwareInventory:

    find this line of code:

    (

     

    SELECT LTRIM(COALESCE(CAST (CAST( v.[adapter_ram] AS int ) / 1024 /1024 as nvarchar(256)),[dbo].get_str(2101, @culture_info))) + CHAR(10) AS [text()]

     

    and change AS int to AS bigint

    that should fix it.

    Thanks,
    Jay

    • Marked as answer by tjacobs42 Monday, November 30, 2009 2:52 PM
    Saturday, November 28, 2009 12:32 AM

All replies

  • I modified the title - as it seems not to be a problem with the number of clients. But the problem is still there...
    Friday, November 20, 2009 11:05 AM
  • This is what I assume you are attempting to create that support backup for…

     

    Once you do get the backup please do send to mapfdbk@microsoft.com so that we may investigate this issue.

     

     

    Thanks,

    Eric

     

    Saturday, November 21, 2009 12:34 AM
  • actually I was using this for backup purposes and to get the maps database transferred to my own computer (as it was working smoothly on XP). I'm not sure if I will be allowed to send you the full inventory. Any other possible troubleshooting method?

    Thanks,
    Torsten.
    Tuesday, November 24, 2009 10:44 AM
  • you can repeatedly edit the dynamic sql query at the end of the sp_report_GetHardwareInventory stored procedure by removing one column at a time until you find the column that contains the offending character.

    Or, you can copy the SQL out of that sproc and run it in query analyzer to see which column is blowing up (but since you mentioned in another post you don't have SQL tools installed, both of these options may be painful to you).

    My guess is that it's one of the following columns:

    Video Card Memory  - int
    System Memory - bigint
    Disk Drive Size - bigint

    Once you figure out what column it is, you then have to figure out which row is causing the problem.  I usually do a binary search using TOP to figure out which row it is if you have many rows....

    Tuesday, November 24, 2009 5:45 PM
  • When I remove "hi.[Video Card Memory]" from the query, it works.Could the following result be the problem? The adapter RAM is too big? (The query I used is below)

    device_number          - 5A759E44 (abbrev'd)
    Computer Name         - computer349
    Video Card                - <name>Mobile Intel(R) 4 Series Express Chipset Family</name>
    Video Card Memory    - <adapter_ram>3234119680</adapter_ram>


    BR,
    Torsten.
    ---------------------------------
     SELECT d.[device_number] AS [device_number],
             [dbo].GetDeviceNameForDisplay(d.[device_number]) [Computer Name],
             (SELECT v.[name]
              FROM [dbo].[video_controllers] AS v
              WHERE v.[device_number] = d.[device_number]
              ORDER BY v.name
              FOR XML PATH ('')) AS [Video Card],
             (SELECT v.[adapter_ram]
              FROM [dbo].[video_controllers] AS v
              WHERE v.[device_number] = d.[device_number]
              ORDER BY v.[adapter_ram]
              FOR XML PATH ('')) AS [Video Card Memory]
     FROM [dbo].[devices] d
        INNER JOIN  [dbo].[hardware_assessment] h on d.[device_number] = h.[device_number]
            AND h.[product_type]<>4    -- Exclude non-windows devices
        INNER JOIN [dbo].[categorized_devices_view] v
            on h.[device_number] = v.[device_number]
        LEFT OUTER JOIN (SELECT [device_number], SUM(m.[capacity]) AS Memory
                           FROM [dbo].[physical_memory] m
                         GROUP BY device_number) x on d.[device_number] = x.[device_number]
    Friday, November 27, 2009 2:54 PM
  • Looks like Moore's law caught up with us; MAP was assuming video adapters would have 2GB or less of RAM.

    To fix this, modify the function dbo.GetHardwareInventory:

    find this line of code:

    (

     

    SELECT LTRIM(COALESCE(CAST (CAST( v.[adapter_ram] AS int ) / 1024 /1024 as nvarchar(256)),[dbo].get_str(2101, @culture_info))) + CHAR(10) AS [text()]

     

    and change AS int to AS bigint

    that should fix it.

    Thanks,
    Jay

    • Marked as answer by tjacobs42 Monday, November 30, 2009 2:52 PM
    Saturday, November 28, 2009 12:32 AM
  • Yepp, that was the place to fix it. Now it works.

    Hope, you'll take this to the next release of the tool.

    BR,
    Torsten.
    Monday, November 30, 2009 2:52 PM
  • Thanks for confirming, the fix is already checked in for the next release....
    Monday, November 30, 2009 4:33 PM