User runs a consolidation. User opens the batch queue, and notices that the consolidation "Finished with Error". User double-clicks on the batch job and there is an error in the "details" section.
The message may vary slightly, but it will be similar to:
Connectivity error: [Microsoft][SQL Native Client][SQL Server]Violation of PRIMARY KEY constraint 'PKNC_XDB12'. Cannot insert duplicate key in object 'cognos.xdb12'
Scenario #3 - inside the "logvfp.log" file (located on the Controller application server)
COGNOS CONTROLLER Technical log - Consolidation modules
***** Date: 2012/09/11 Time: 11:17:40 *****
1 - PROCEDURE MAIN.RUNKONSOL C:\PROGRAM FILES (X86)\IBM\COGNOS\C10\SERVER\LIBS\FRANGOLIB.VCT - Line 86
2 - PROCEDURE KONSOLPS KONSOLPS.FXP - Line 555
3 - EVENTMAINS.FXP - Line 83
4 - FEVENT005S.FXP - Line 102
5 - PROCEDURE FEVENT0059 FEVENT005S.FXP - Line 834
6 - FEVENTUPDS.FXP - Line 195
7 - FPSQL.FXP - Line 329
8 - PROCEDURE INSSQLXDB FPSQL.FXP - Line 400
9 - PROCEDURE FSQLERR FSQLERR.FXP - Line 86
Program statement: Not available, c:\program files (x86)\ibm\cognos\c10\server\progs\fsqlerr.prg not found
Error number: 2005
Message text: Connectivity error: [Microsoft][SQL Native Client][SQL Server]Violation of PRIMARY KEY constraint 'PKNC_XDB12'. Cannot insert duplicate key in object 'cognos.xdb12'.
There are several possible causes for the generic error "Violation of PRIMARY KEY constraint 'PKNC_XDBxx'":
- Scenario #1 - Automatic journals were migrated from consolidator where a number was not needed. In Controller a number is required.
- For more details, see separate IBM Technote #1335835.
- Scenario #2 - Customer had uploaded a transaction currency which was not logical.
- For more details, see separate IBM Technote #1361654.
- Scenario #3 - Code production problem (APAR reference PM72924) triggered when using event E800 (Swedish method for moving amounts between restricted and unrestricted equity).
- This is triggered by changes in the company structure
- TIP: E800 is very rarely used, even in Sweden, after IFRS has gained popularity
Resolving the problem
Update automatic journals
- See separate IBM Technote #1335835.
Contact IBM Support for more assistance.
- See separate (internal) IBM Technote #1361654.
Upgrade to Controller 10.1.1 FP1 (10.1.1671) or later.
Inactivate E800 for the OS structure.
- NOTE: The consolidation will then run OK, but no etyp 80 journals will be created.
- Therefore, this will have to be handled manually (if necessary).
- Click "Maintain - Configuration - Automatic Journals - Define"
- Select E800
- Tick/check "Exclude the Automatic Journal from the Specific Consolidation Types"
- Mark "OS" in the list