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

Technote (troubleshooting)


Problem(Abstract)

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.

Symptom

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'.


Cause

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

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

Related information

1335835 - Error consolidating with status: Violation of
APAR PM72924
1616343 - Cognos Controller 10.1.1 FP1 now available

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

Cognos Controller
Controller

Software version:

8.3, 8.4, 8.5, 8.5.1, 10.1, 10.1.1

Operating system(s):

Windows

Reference #:

1610677

Modified date:

2012-11-23

Translate my page

Machine Translation

Content navigation