Intermittent "Standard error ... -2147467261 ... interop.Excel ... This is often an indication that other memory is corrupt" when running large reports.
User opens a large spreadsheet (Excel link report) inside Excel. User clicks F9 to run the report. This may work OK. However, if they re-run the report then they may receive an error message. Therefore, the error may appear to be intermittent.
* In one real-life case, the error occurred on the second or third re-run of F9 (second or third time that they ran the report)
Problem does not affect all users (or all spreadsheets).
Description: An attempt was made to protected memory read or write. This is often an indication that other memory is corrupt.
at Excel._Worksheet.set_EnableCalculation (Boolean RHS)
at Cognos.Controller.Reports.XlLinkLayout.RecalcXL (Int32 lVersion,
Event Viewer (Application Log)
Event Type: Warning
Event Source: Cognos Controller
Event Category: None
Event ID: 0
Time: 11:37:38 AM
Description: Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
at Excel._Worksheet.set_EnableCalculation(Boolean RHS)
at Cognos.Controller.Reports.XlLinkLayout.RecalcXL(Int32 lVersion, Boolean bActiveSheetOnly)
There are other possible causes for similar errors/symptoms.
- TIP: For more examples, see separate IBM Technote #1665528.
This Technote specifically relates to the scenario where the cause is a code production problem (APAR PM84020) in Controller (versions from 10.1.1308 onwards) which only affects end users whose client operating system is based on either Windows XP or Windows 2003.
Controller 10.1.1308 and many later versions of 10.1.1.
In addition, in one real-life customer's case, the problem was only reproducible when both of the following were true:
- running the Controller client on a Windows XP device (with 2GB RAM, using Excel 2003 SP3)
- running large reports (for example one defined with 6 tabbed sheets and over 100 rows).
In that same real-life case the customer solved the problem by upgrading the client device(s) to Windows 7 (with Office 2007 and minimum 4 GB RAM).
Resolving the problem
The fix depends on what version of Controller you are currently using:
(1) Controller versions between 10.1.1305 and 10.1.1703 (inclusive)
- Upgrade to Controller 10.1.1707.
(2) Controller 10.1.2155 or later.
- Upgrade to Controller 10.1.2659 or later.
There are several possible workarounds:
- Method #1 (Recommended) - Upgrade client device to either Windows 7 (laptops / desktop PCs) or Windows 2008 (Citrix/Terminal Servers) as appropriate.
This should ensure that users *never* see the error, and therefore is (by far) the most recommended solution.
- Method #2 - Exit Excel and re-launch Excel when receive the error.
Typically the user will only see the error after refreshing reports several times (for example 3 or 4 times). If the end user closes Excel (after receiving the error) and then re-launches Excel (and re-runs the report) then they will not see the error.
- Method #3 - Simplify the spreadsheet and/or enable Optimise2
By reducing the complexity of the spreadsheet (or enabling Optimise2 - see Technote 1347048) this will have the effect of reducing the strain on the system (and therefore reducing the frequency of when the error message is seen).