Issue | When both of the following occur:
- Users sporadically receive the following error message while using the Archer site:
This page cannot be displayed
- In the Windows event log for the web servers, the following error is logged:
Faulting application name: w3wp.exe, version: 7.5.7601.17514, time stamp: 0x4ce7afa2 Faulting module name: clr.dll, version: 4.0.30319.36392, time stamp: 0x58dc633a Exception code: 0xc00000fd Faulting application path: c:\windows\system32\inetsrv\w3wp.exe Faulting module path: C:\Windows\Microsoft.NET\Framework64\v4.0.30319\clr.dll
|
Resolution | To resolve the issue, identify values lists that have data items that are nested over 50 items deep
- The followng query will find Values lists that are over 50 items deep:
WITH selectValues AS ( SELECT 0 generation, * FROM dbo.tblIVSelectValue sv WHERE sv.parent_select_value_id IS NULL UNION ALL SELECT r.generation + 1, sv.* FROM dbo.tblIVSelectValue sv JOIN selectValues r ON sv.parent_select_value_id = r.select_value_id ) SELECT MAX(sv.generation) as NestedCount, sv.select_id, sdt.select_def_type_name, sdt2.select_name FROM selectValues sv with(nolock) JOIN dbo.tblIVSelectDef sd with(nolock) ON sd.select_id = sv.select_id JOIN dbo.tblIVSelectDefType sdt with(nolock) ON sdt.select_def_type_id = sd.select_def_type_id JOIN dbo.tblIVSelectDefTranslation sdt2 with(nolock) ON sdt2.select_id = sd.select_id AND sdt2.is_default = 1 LEFT JOIN tblValueListValueTranslation VLVT with(nolock) on VLVt.select_value_id = sv.select_value_id GROUP BY sv.select_id, VLVT.select_value_name, sdt.select_def_type_name, sdt2.select_name HAVING MAX(sv.generation) > 50 ORDER BY 1 DESC option (maxrecursion 32000);
- Restructure the values list(s) to not be structured too deeply.
- Identify calculations that contain formulas that are nested over 40 functions dee. The following query will find calculations that are nested over 40 functions deep:
SELECT ft.field_name, mt.module_name from tblCAlc c LEFT JOIN tblFieldTranslation ft on c.field_id = ft.field_id LEFT JOIN tblIVFieldDef fd on fd.field_id = c.field_id LEFT JOIN tblLevel l on l.level_id = fd.level_id LEFT JOIN tblModuleTranslation mt on mt.module_id = l.module_id WHERE formula like '%))))))))))))))))))))))))))))))))))))))))))%'
- Split the calculation into multiple separate calculated fields so the formula does not contain items that are nested too deeply.
|