Rapid Mart ( Purchase violation of primary key co...

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" )

(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

SAP Managed Tags:
SAP Data Services

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Accepted Solutions (0)

Answers (3)

Hi Fasi,

Not Active Contributor Xiaoming Wu 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