Troubleshooting
Problem
User opens Excel link (.XLS) spreadsheet file. User clicks 'Controller - Refresh F9' to run the report. After a short time (e.g. a few seconds), user receives error message.
Symptom
The error will vary slightly depending on the environment.
Example #1:
- Information
Standard Error
Number: 5
Source: FrangoDirect.ExcelLinkD.FetchValuesBulk#ControllerProxyClient
Description: System.Web.Services.Protocols.SoapException: Server was unable to process request. --->
System.Runtime.InteropServices.COMException (0x80040E14): Cannot bulk load because the file
\\SQLservername\sharename\FA06608C6.tbl could not be opened. Operating system error code 5(Access is denied.).
Server: SQLservername Share: sharename User: Psw: ***** Transfer: 0 Prefix: E398E5F71CAC403186024A8F...
Direct Caller: E398E......
at Cognos.Controller.Proxy.CCRWS.ExcelLinkB_FetchValuesBulk(String sGuid, String sKeys, String sMcurr, Boolean
bLocLang, String sUser, String sRep, Boolean bIsLnk, String sSortOrd, Int32 lOptBitSet)
--- End of innter exception stack trace ---
OK
Event Log:
- Event Type: Warning
Event Source: Cognos Controller
Event Category: None
Event ID: 0
Date: 22/04/2008
Time: 14:42:38
User: N/A
Computer: SERVERNAME
Description:
Error occured at 22/04/2008 14:42:38 in Microsoft Office 2003, Error No=5, Source=FrangoDirect.ExcelLinkD.FetchValuesBulk#ControllerProxyClient, Description=System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Runtime.InteropServices.COMException (0x80040E14): Cannot bulk load because the file "\\SQLSERVER\sharename\F7BE20426.tbl" could not be opened. Operating system error code 5(Access is denied.). Server: SQLSERVER Share: sharename User: domainname\COM+_username Psw: ***** Transfer: 0 Prefix: 6EF9CDA450074C0C85805D9101F75A66 Direct Caller: 6EF9CDA450074C0C85805D9101F75A66
at Cognos.Controller.Proxy.CCRWS.ExcelLinkB_FetchValuesBulk(String sGuid, String sKeys, String sMcurr, Boolean bLocLang, String sUser, String sRep, Boolean bIsLnk, String sSortOrd, Int32 lOptBitSet)
--- End of inner exception stack trace ---, HelpFile= HelpContext=0
Example #2: (Seen when the NTFS permissions denied the MSSQL Windows service rights to read the files)
Cannot bulk load. The file "\\server\share\randomfile.tbl" does not exist.
Cause
Incorrect settings used for ERO/Optimise2 feature.
More Information:
Enhanced Reporting Optimisation (ERO - sometimes refererred to as 'Optimise2') is an optional feature used to speed up spreadsheet reports.
- The ERO mechanism involves a file to be copied from the Application server to the Database server (e.g. SQL), before this file is 'bulk loaded' into the database.
- This requires certain permissions (for example Windows 'share' and 'NTFS' permissions) otherwise it will fail.
The error is being triggered because the SQL server is attempting to read the file '\\SQLservername\sharename\FA06608C6.tbl', but is being disallowed by the operating system. There are several potential scenarios for this:
- Scenario #1 - Share permissions (on \\SQLservername\sharename) deny read access for the user specified
- Scenario #2 - NTFS permissions (on the folder which contains the file, for example C:\Optimise2)
- Scenario #3 - (Rare) 3rd party program (e.g. AntiVirus software) denying access to the file FA06608C6.tbl.
Environment
Spreadsheet is using Optimise2 (also known as ERO / Enhanced Reporting Optimisation).
Resolving The Problem
Fix:
Check all of the following possible causes/scenarios:
- Scenario #1 - Correct the share permissions
- (a) the user specified inside section 'Enhanced Reporting Optimization' in 'Controller Configuration' on the Application server
- (b) and the Windows user which is running the SQL server service.
- Scenario #2 - Correct the NTFS permissions
- (a) the user specified inside section 'Enhanced Reporting Optimization'
- (b) and the Windows user which is running the SQL server service.
- Scenario #3 - (Rare) Modify any 3rd party program (e.g. AntiVirus software) denying access to the file FA06608C6.tbl.
Specifically, reconfigure the share permissions on \\SQLservername\sharename to allow read/write access for
Specifically, reconfigure the NTFS file permissions for the folder which hosts the share \\SQLservername\sharename to allow read/write access for
Specifically, reconfigure your 3rd party program to exclude scanning of the folder which hosts the share \\SQLservername\sharename.
Scenario #1 - share permissions:
The following instructions are based on Windows 2012 and SQL 2012.
- If your system uses different versions of Windows/SQL, the instructions will need to be altered slightly.
1. Logon to the Controller application server as a Windows administrator
2. From the 'start menu', launch 'Controller Configuration'
3. Open the section 'Enhanced Reporting Optimization'
4. Inside 'Select connection', choose the correct 'database connection' name
5. Make a note of the setting 'User ID' (e.g. 'domain\controller_service')
- TIP: If there is no user specified (i.e. it is blank), then the username is the same as the COM+ username. This can be found inside the section 'COM+ Server' under the value 'Specify Account'
6. Check that the Windows user (e.g. 'domain\controller_service') account is valid and has no restrictions on it (e.g. check that it is not locked out, or that the password requires changing etc.).
- TIP: You can check by attempting to logon to the application server using this 'domain\controller_service' Windows account.
Afterwards:
7. Logon to the server (e.g. SQLSERVER) where the share resides
8. Locate the folder (e.g. D:\Optimise2) which is being used as the share
9. Right-click on folder, and choose 'Properties'
10. Click on 'sharing' tab
11. Ensure that correct share name (e.g. 'Optimise2$') is displayed inside 'share name'
12. Click 'permissions'
13. Ensure that the COM+ user (e.g. DOMAIN\Controller_system) has 'full control' permissions
14. Launch Windows Services, and check to see which Windows user (e.g. "DOMAIN\SQL_Service" or "NT AUTHORITY\System" or "NT Service\MSSQLSERVER') the SQL server service (e.g. 'MSSQLSERVER') is using:
15. Repeat the earlier step (13) to ensure that this user (the one that runs the SQL server) also has has 'full control' permissions to the share.
- TIP: Alternatively, as a quick and easy 'test' workaround, you can simply add the group 'Everyone' and give this group 'full control' permissions to the share permissions.
Scenario #2 - NTFS permissions:
The following instructions are based on Windows 2012 and SQL 2012.
- If your system uses different versions of Windows/SQL, the instructions will need to be altered slightly.
1. Check the value of 'User ID' inside the section 'Enhanced Reporting Optimization' (see above for instructions)
2. Logon to the SQL server (e.g. 'SQLSERVER') where the share resides
3. Locate the folder (e.g. D:\Optimise2) which is being used as the share
4. Right-click on folder, and choose 'Properties'
5. Click on the 'Security' tab:
6. Ensure that the COM+ user (e.g. DOMAIN\Controller_system) has 'full control' permissions
7. Launch Windows Services, and check to see which Windows user (e.g. "DOMAIN\SQL_Service" or "NT AUTHORITY\System" or "NT Service\MSSQLSERVER') the SQL server service (e.g. 'MSSQLSERVER') is using:
8. Repeat the earlier step (6) to ensure that this user (the one that runs the SQL server service) also has has 'full control' permissions to the share.
- TIP: Alternatively, as a quick and easy 'test' workaround, you can simply add the group 'Everyone' and give this group 'full control' permissions to the NTFS permissions.
Scenario #3 - Anti Virus:
See 3rd party product documentation.
================================================================
Workaround:
Disable the use of Optimise2 for this spreadsheet.
- TIP: Optimise2 is designed to simply speed up the refreshing of reports, so the only side-effect of disabling Optimise2 is to slow down the speed of running of this report.
Steps:
The following instructions are based on Excel 2007.
- The instructions may need to be modified slightly if you are using a different version of Excel.
1. Launch Microsoft Excel
2. Open the 'bad' spreadsheet
3. Click the tab 'Formulas'
4. Click 'Name Manager'
5. Highlight the entry 'Optimise2' (which refers to Sheet1, cell A1)
6. Click 'Delete':
7. Save changes and test.
[{"Product":{"code":"SS9S6B","label":"Cognos Controller"},"Business Unit":{"code":"BU002","label":"Business Analytics"},"Component":"Controller","Platform":[{"code":"PF033","label":"Windows"}],"Version":"10.2.0;10.1.1;10.1;8.5.1;8.5;8.4;8.3","Edition":""}]
Historical Number
1038889
Document Information
Modified date:
15 June 2018
UID
swg21347757