** Troubleshooting ** "Connectivity error ...Violation of PRIMARY KEY constraint 'PKNC_XDBxx'" when running a consolidation

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 *****
Call Stack:
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

Scenario #1

Update automatic journals

• See separate IBM Technote #1335835.

Scenario #2

Contact IBM Support for more assistance.

• See separate (internal) IBM Technote #1361654.

Scenario #3

# Fix:
Upgrade to Controller 10.1.1 FP1 (10.1.1671) or later.

# Workaround:
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).

Steps:
1. Click "Maintain - Configuration - Automatic Journals - Define"
2. Select E800
3. Tick/check "Exclude the Automatic Journal from the Specific Consolidation Types"
4. Mark "OS" in the list