3 Replies Latest reply: May 21, 2012 5:19 PM by Norbert Klein RSS

Rapid Mart  ( Purchase violation of primary key constraint

mohammed fasiur rahman
Currently Being Moderated

Hi Friends
 
One of the purchasing rapid mart got failed after few minutes of processing, when i check the log I found the following error
 
1) Violation of PRIMARY KEY constraint
2)Cannot insert duplicate key in object
 
Please find below the Log error
 
<<PTH-SAPSQL03.xxx.local>
 
Log: error_05_17_2012_20_20_03_901__83e6c6a4_9ac7_4c04_af8e_b32a1d86f0de.txt Job Server: PTH-SAPBO01:3500
 
Job name: Purchasing_Rapidmart_SAP
(12.2) 05-17-12 22:19:59 (W) (2376:10764) FIL-080102: |Data flow DF_FiscalPeriodNotYrSpecific_SAP|Reader R3_FiscPer_NOT_YrSpecific End of file was found without reading a complete row for file <//PTH-SAPERP01/sapXXX/trans/fiscalperiods.dat>. The expected number of columns was <8> while the number of columns actually read was <6>. Please check the input file for errors or verify the schema specification for the file format. The number of rows processed was <64016>. (12.2) 05-17-12 22:20:41 (E) (1184:4500) DBS-070404: |Data flow DF_FiscalPeriodDefault_SAP|Reader Query
 
 
SQL submitted to ODBC data source <PTH-SAPSQL03.XXX.local> resulted in error <[Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint
XPK_FISC_PERIOD'. Cannot insert duplicate key in object 'dbo.FISC_PERIOD'.>.
 
 
 
The SQL submitted is <INSERT INTO "dbo"."FISC_PERIOD"

( "FISC_YEAR" , "FISC_PERIOD" , "FISC_YR_VARIANT_ID" , "FISC_MONTH" , "START_DATE" , "END_DATE" , "FISC_QUARTER" , "FISC_HALF" , "CALENDAR_YEAR" , "CALENDAR_MONTH_NUMERIC" , "CALENDAR_MONTH_LONG" , "CALENDAR_MONTH_SHORT" , "CALENDAR_QUARTER" , "CALENDAR_HALF" , "SPECL_PERIOD_FLAG" , "FISC_YR_VARIANT_NAME" , "LOAD_DATE" , "LOAD_TIME" )

 

SELECT DISTINCT  "FISC_PERIOD_STAGE"."FISC_YEAR"  , "FISC_PERIOD_STAGE"."FISC_PERIOD"  , '?' , '?' , CONVERT(DATETIME2, '1900-01-01 00:00:00.000', 121) , CONVERT(DATETIME2, '9999-12-31 00:00:00.000', 121) , '?' , '?' , -1 , '?' , '?' , '?' , -1 , '?' , '?' , '?' , CONVERT(DATETIME2, '2012-05-17 20:20:17.000', 121) , '20:20:17' FROM "dbo"."FISC_PERIOD_STAGE" "FISC_PERIOD_STAGE" >.

(12.2) 05-17-12 22:20:41 (E) (1184:4500) DBS-054003: |Data flow DF_FiscalPeriodDefault_SAP|Reader Query SELECT query <INSERT INTO "dbo"."FISC_PERIOD" ( "FISC_YEAR" , "FISC_PERIOD" , "FISC_YR_VARIANT_ID" , "FISC_MONTH" , "START_DATE" , "END_DATE" , "FISC_QUARTER" , "FISC_HALF" , "CALENDAR_YEAR" , "CALENDAR_MONTH_NUMERIC" , "CALENDAR_MONTH_LONG" , "CALENDAR_MONTH_SHORT" , "CALENDAR_QUARTER" , "CALENDAR_HALF" , "SPECL_PERIOD_FLAG" , "FISC_YR_VARIANT_NAME" , "LOAD_DATE" , "LOAD_TIME" )  SELECT DISTINCT "FISC_PERIOD_STAGE"."FISC_YEAR"  ,  "FISC_PERIOD_STAGE"."FISC_PERIOD"  , '?' , '?' , CONVERT(DATETIME2, '1900-01-01 00:00:00.000', 121) , CONVERT(DATETIME2, '9999-12-31 00:00:00.000', 121) , '?' , '?' , -1 , '?' , '?' , '?' , -1 , '?' , '?' , '?' , CONVERT(DATETIME2, '2012-05-17 20:20:17.000', 121) , '20:20:17' FROM  "dbo"."FISC_PERIOD_STAGE" "FISC_PERIOD_STAGE" > failed.

 

The database error message is <SQL submitted to ODBC data source <PTH-SAPSQL007.XXX.local> resulted in error <[Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint 'XPK_FISC_PERIOD'. Cannot insert duplicate key in object 'dbo.FISC_PERIOD'.>. The SQL submitted is <INSERT INTO "dbo"."FISC_PERIOD" ( "FISC_YEAR" , "FISC_PERIOD" , "FISC_YR_VARIANT_ID" , "FISC_MONTH" , "START_DATE" , "END_DATE" , "FISC_QUARTER" , "FISC_HALF" , "CALENDAR_YEAR" , "CALENDAR_MONTH_NUMERIC" , "CALENDAR_MONTH_LONG" , "CALENDAR_MONTH_SHORT" , "CALENDAR_QUARTER" , "CALENDAR_HALF" , "SPECL_PERIOD_FLAG" , "FISC_YR_VARIANT_NAME" , "LOAD_DATE" , "LOAD_TIME" )

 

SELECT  DISTINCT "FISC_PERIOD_STAGE"."FISC_YEAR"  ,  "FISC_PERIOD_STAGE"."FISC_PERIOD"  , '?' , '?' , CONVERT(DATETIME2, '1900-01-01 00:00:00.000', 121) , CONVERT(DATETIME2, '9999-12-31 00:00:00.000', 121) , '?' , '?' , -1 , '?' , '?' , '?' , -1 , '?' , '?' ,
'?' , CONVERT(DATETIME2, '2012-05-17 20:20:17.000', 121) , '20:20:17' FROM "dbo"."FISC_PERIOD_STAGE" "FISC_PERIOD_STAGE" >.>.
(12.2) 05-17-12 22:20:41 (E) (11324:6880) DBS-054003: |Data flow DF_FiscalPeriodDefault_SAP|Reader Query SELECT query <INSERT INTO "dbo"."FISC_PERIOD" ( "FISC_YEAR" , "FISC_PERIOD" , "FISC_YR_VARIANT_ID" , "FISC_MONTH" , "START_DATE" , "END_DATE" , "FISC_QUARTER" , "FISC_HALF" , "CALENDAR_YEAR" , "CALENDAR_MONTH_NUMERIC" , "CALENDAR_MONTH_LONG" , "CALENDAR_MONTH_SHORT" , "CALENDAR_QUARTER" , "CALENDAR_HALF" , "SPECL_PERIOD_FLAG" , "FISC_YR_VARIANT_NAME" , "LOAD_DATE" , "LOAD_TIME" )  SELECT DISTINCT "FISC_PERIOD_STAGE"."FISC_YEAR"  ,  "FISC_PERIOD_STAGE"."FISC_PERIOD"  , '?' , '?' , CONVERT(DATETIME2, '1900-01-01 00:00:00.000', 121) , CONVERT(DATETIME2, '9999-12-31 00:00:00.000', 121) , '?' , '?' , -1 , '?' , '?' , '?' , -1 , '?' , '?' , '?' , CONVERT(DATETIME2, '2012-05-17 20:20:17.000', 121) , '20:20:17' FROM  "dbo"."FISC_PERIOD_STAGE" "FISC_PERIOD_STAGE" > failed. The database error message is <SQL submitted to ODBC data source <PTH-SAPSQL007.XXX.local> resulted in error <[Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint 'XPK_FISC_PERIOD'. Cannot insert duplicate key in object 'dbo.FISC_PERIOD'.>. The SQL submitted is <INSERT INTO "dbo"."FISC_PERIOD" ( "FISC_YEAR" , "FISC_PERIOD" , "FISC_YR_VARIANT_ID" , "FISC_MONTH" , "START_DATE" , "END_DATE" , "FISC_QUARTER" , "FISC_HALF" , "CALENDAR_YEAR" , "CALENDAR_MONTH_NUMERIC" , "CALENDAR_MONTH_LONG" , "CALENDAR_MONTH_SHORT" , "CALENDAR_QUARTER" , "CALENDAR_HALF" , "SPECL_PERIOD_FLAG" , "FISC_YR_VARIANT_NAME" , "LOAD_DATE" , "LOAD_TIME" )  SELECT
DISTINCT "FISC_PERIOD_STAGE"."FISC_YEAR"  ,  "FISC_PERIOD_STAGE"."FISC_PERIOD"  , '?' , '?' , CONVERT(DATETIME2, '1900-01-01
00:00:00.000', 121) , CONVERT(DATETIME2, '9999-12-31 00:00:00.000', 121) , '?' , '?' , -1 , '?' , '?' , '?' , -1 , '?' , '?' , '?' , CONVERT(DATETIME2, '2012-05-17 20:20:17.000', 121) , '20:20:17' FROM "dbo"."FISC_PERIOD_STAGE" "FISC_PERIOD_STAGE" >.>.
 
Please help in resolving this error.
 
Thanks in advance
fasi

  • Re: Rapid Mart  ( Purchase violation of primary key constraint
    Xiaoming Wu
    Currently Being Moderated

    Hi,

     

    Please check KB 1509533 to see if it can help.

     

    Thanks

    Helen

  • Re: Rapid Mart  ( Purchase violation of primary key constraint
    Ginger Gatling
    Currently Being Moderated

    Hi - is this a 4.0 Rapid Mart?   Have you entered a ticket for this yet?

    -ginger

  • Re: Rapid Mart  ( Purchase violation of primary key constraint
    Norbert Klein
    Currently Being Moderated

    Hi Fasi,

     

    Not Active Contributor has provided the correct solution for your problem.

     

    Using ABAP data flows your DS will create an ABAP program that will be loaded and executed by SA ECC ( or BW..depending on where you connect to).

    That ABAP program will dump the information it collected to a text file, in your case

     

    //PTH-SAPERP01/sapXXX/trans/fiscalperiods.dat

     

    that DS will read.

    Your text file has some characters it "should not have" in field

    6 if row 64016.

     

    DS knows that your  rows should have 8 columns, so it warns.

     

    Next step: Try to read that file (e.g. Notepad, Notepad++, Excel..) and see what possible character is contained in that field (or previous ones , max 1 line before this one) to make DS mismatch the code.

     

    In the example of the resolution the field contained an un-escaped " , and text fields were also sorrounded with "" .

     

    I can't say for sure, but your primary key issue probably derives from that, especially because the all the problems contain fical year.. and will probably be solved once the SAP problem has been solved.

     

    Solution: Really depends on what you find and how often this happens. You can change the settings in DataServices to use different characters in the ABAP and then in the text file format, but if this only happens very few times..ask the SAP guys to make sure these characters can not be put into that column.

     

     

     

    Regards

     

    Norbert

Actions