000035888 - RSA Archer: IIS Webserver process w3wp.exe crashes with 0xc00000fd and users sporadically receive "This Page Cannot Be Displayed" error

1. Users sporadically receive a "This page cannot be displayed" error message while using the Archer site.
2. 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

 

1. Identify values lists that have data items that are nested over 50 items deep

The below 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);

2. Restructure the values list(s) to not be structured too deeply.
3. Identify calculations that contain formulas that are nested over 40 functions deep

The below 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 '%))))))))))))))))))))))))))))))))))))))))))%'

4. Split the calculation into multiple separate calculated fields so the formula does not contain items that are nested too deeply.