Product:
Cognos Controller 10.3 version 10.3.1.45 (Integration Version 10.3.1.19 Install/Udater Kit Version 10.3.1.36)
Microsoft Windows 2012 R2 server
Microsoft SQL 2014 database server
Problem:
When running a excel report or updating a excel form you get a error message. If you turn off ERO by remove the “Optimise2” name on cell A1 in the spreadsheet then it works. This is a upgrade installation from Cognos Controller 10.2.1 where it was no problem to run a ERO report in old version of Cognos Controller.
Error message:
System.Web.Services.Protocols.SoapException: Server was unable to process request. — System.Exception: Cannot bulk load because the file “xxx\xxx.tbl” could not be opened. Operating system error code 3(The system cannot find the path specified.). at Cognos.Controller.Proxy.CCRWS.ExcelLinkB_FetchValuesBulk(String sGuid, String sKeys, String sMcurr, Boolean bLocLang, string sUser, String sRep, Boolean bIsLnk, String sSortOrd, String sParams)
—End of inner exception stack trace—
Solution:
Ensure you leave the “server directory” field blank at Enhanced Reporting Optimizations tab in Controller Configuration.
With SQL databases the use of a directory folder in ERO, is not longer supported.
For Cognos Controller 10.3, there is less need to use ERO with SQL databases. But you still need to set it up for upgrading customers, so they can run there old Excel reports.
How setup ERO for SQL database:
Ensure that the SQL account used to access the Cognos Controller databases have Server Roles “bulkadmin” and “public”.
Ensure the SQL services is run by a windows domain account and not as LocalSystem.
Create a folder on your controller Windows server called d:\ero
Right click on the ERO folder and select Properties.
Click on Sharing tab.
Click on Advanced Sharing button.
Mark “Share this folder”
Enter the share name to be: ERO$
(The dollar sign make the share invisible for browsing users)
Click on Permissions button.
Click “Allow” at Full Control for user “Everyone” (or add the service account that should have access here).
Click Apply and OK.
Click Apply and OK.
Click on Security tab.
Click on Edit button.
Click Add button.
Enter the name of the service account and press Check Names button.
Click OK.
Click “Allow” at Full Control for the service account you just added.
Click Apply and OK.
By default members of the servers local Administrator group have full control to a folder. You can remove the users group to ensure other have no access.
Click Apply and OK.
Click Close. The share is created.
Open Controller Configuration.
Go to the Enhanced Reporting Optimizations tab.
Select the controller database from the connection drop down list.
Fill in for each database;
File Mode: File Copy
Server: the name of the server where the ERO share is — normally the controller server or the SQL server.
Share: the name of your share (e.g. “ERO$”)
Server Directory: Must be blank (empty). (if using Oracle you need to fill this in).
User ID: AD\service_account (the windows account that you have given read/write permission to the ERO network share)
Password: password for the above user — it is encrypted when you press enter.
Click on Save icon in Controller Configuration.
Ensure that the EXCEL spreadsheet that will use ERO does not have a space in there spreadsheets tab-name.
In the excel report that should use ERO , go to cell A1.
Click on Name Manager icon.
Enter the name to be Optimise2.
Save the change and spreadsheet.
Update the Controller values by pressing F9 in Excel, when you are logged in to the Controller database through the Controller Excel Link.
Other error messages:
Server was unable to process request. —- System.IO.IOException: The network name cannot be found.
Possible cause:
The ERO share does not exist, check that the folder is shared out and not misspelled in controller configuration.
Other error messages:
Server was unable to process request. —- System.ArgumentException: Access to the path “xxx\xxx\xxx.tbl” is denied.
Possible cause:
The user have only READ access to the share, make sure you have full access for the user doing the request.
Other error messages:
Operating system error code 5(Access is denied.)
Possible cause:
The user account doing the request have not the full access to the folder on hard disk. Check the NTFS rights on the ERO folder.
You define the Active Directory user to access the Windows file share in Cognos Controller Configuration. If this is left blank, the user running the COM object is used – in most cases it is “Local System” account on Windows server.
More Information:
http://www-01.ibm.com/support/docview.wss?uid=swg21347048
http://www-01.ibm.com/support/docview.wss?uid=swg21347757
