process "InventoryResult" Excelsheet fails with arithmetic overflow (MAP 5.0)
- 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.
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 bytjacobs42 Monday, November 30, 2009 2:52 PM
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...
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
- 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. 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....- 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] - 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 bytjacobs42 Monday, November 30, 2009 2:52 PM
- 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. - Thanks for confirming, the fix is already checked in for the next release....
