How to resolve error sapsql_array_insert_duprec

How to resolve error sapsql_array_insert_duprec

In this post I would like to share as how to resolve the error sapsql_array_insert_duprec in SAP systems. I hope many of us seen the error “sapsql_array_insert_duprec” atleast once while working in the SAP systems. Here I would like to summarize all the possible solutions for the error sapsql_array_insert_duprec.

IMPORTANT: The error may occur in various situations, kindly look at the ST22 dump for more information about the terminated program, function module, tables, reports, and transactions, based on the information please refer below for possible solution.

SAPSQL_ARRAY_INSERT_DUPREC in saving settlement rule

When you save the settlement rule, the exception condition SAPSQL_ARRAY_INSERT_DUPREC occurs. The update is terminated.

The record to be updated should have been included, changed or deleted on settlement rule level (table COBRA) or the individual distribution rule levels (table COBRB).

The record to be entered already exists on the database or the records to be changed or deleted do not yet exist.

This means that the data is inconsistent or the calling program sets the wrong processing mode.

Solution:

Implement the program modification according to the attached correction instructions in SAP Note 1678494

After the program change, the record to be updated can be entered into the settlement rule tables without a runtime error.

However, it remains unclear why the system accesses an existing record with INSERT or non-existent records with UPDATE or DELETE.

The cause for the dump is usually hard to find because the update of the settlement rule is called after programs whose processing mode has to be set correctly are complete.

Retain the modification from the correction instructions in the program for as long as required.  However, for an upgrade or something similar, you should not implement it.  It makes the update of the settlement rule slightly slower.

Integration model generation Error SAPSQL_ARRAY_INSERT_DUPREC

Although you have implemented SAP Notes 2115941 and 2148599, certain background jobs for generating integration models terminate because the runtime error SAPSQL_ARRAY_INSERT_DUPREC occurs.

ou execute at least two background jobs in parallel to generate integration models for the same data object (for example, the material master). In addition, some materials are contained in both integration models.

For example, the integration models X and Y both contain the material A with the plant 1000 and you generate the two integration models X and Y in parallel.

This procedure may be necessary for master data if multiple SCM systems (for example, an APO system and an EWM system) are connected with your ERP system.

Implement the correction instructions contained in this SAP Note 2171503 in your ERP system.

SAPSQL_ARRAY_INSERT_DUPREC while posting Ad Valorem Valuation document

Customers having installed and enabled the Roadmap version of Revenue Distribution are experiencing the following issues while posting Ad Valorem document :

Transaction /PRA/ADV_DOC throws dump error SAPSQL_ARRAY_INSERT_DUPREC for NGL materials while updating reversal keys when posting Ad Valorem valuation document.

Transaction /PRA/ADV_DOC throws dump error ITAB_ILLEGAL_SORT_ORDER for materials other than NGL when posting Ad Valorem valuation document.

  • Apply the corrections attached in the Note. 2416053

SAPSQL_ARRAY_INSERT_DUPREC in the SAP_NOTIFADMIN_SYNC_DLS job

You are using Central Notification Management in SAP Solution Manager Workcenter.

A runtime error SAPSQL_ARRAY_INSERT_DUPREC is generated when executing the job SAP_NOTIFADMIN_SYNC_DLS.

Implement the attached correction instruction in the note 2418898 or import the latest ST Support Package

sapsql_array_insert_duprec while storing or saving the documents

The error “sapsql_array_insert_duprec” may occur including with function module CV200_DB_DRAO_UPDATE’ while check-in and save the documents with below error information.

Runtime Error: sapsql_array_insert_duprec

Except: CX_SY_OPEN_SQL_DB

Possible termination at DRAO while inserting as below

INSERT DRAO FROM TABLE LT_DRAO.

IF SY-SUBRC <> 0.

MESSAGE A178 WITH tDRAO insert’ SY-SUBRC.

ENDIF.

Solution and Reason: The problem arises when original files are not saving at content server especially larger files which are consuming more than 9999 rows in the table. If you are storing any original document that will be saved in DRAO table, one original document can have maximum 9999 rows in a table. The dump will occur if you are trying to store larger originals which are consuming more than 9999 rows in the table, because this is not possible.

You can check the setting in DC10 transaction “use KPRO” is set for the required document type. SAP do not recommends storing such larger documents in SAP Database.

SAPSQL_ARRAY_INSERT_DUPREC (Program SAPLCLVF) when saving a document

You may also get a same error when saving the documents with program SAPLCLVF.

Runtime Errors: SAPSQL_ARRAY_INSERT_DUPREC

Except: CX_SY_OPEN_SQL_DB

ABAP Program: SAPLCLVF

Application Component: CA-CL-CL

As already mentioned please look for the termination point.

|   31|  if syst-tfill ne 0.                                                                       |

|>>>>>|    insert kssk from table hkssk.                                                           |

|   33|    if syst-subrc ne 0.                                                                     |

|   34|      message a585 with tabkssk.                                                            |

|   35|    endif.                                                                                  |

|   36|  endif.                                                                                    |

Reason and Solution: If the termination occurs when inserting a dataset or record in KSSK table then the problem could be due to inconsistency or duplicate entries in the table KSSK.

You can use run the report RCCLZUOB in SE38 to clean up the inconsistencies. You can use below selection for DMS purpose.

Report RCCLZUOB

 

SAPSQL_ARRAY_INSERT_DUPREC while creating purchase order in ME21, ME59n

Many of the users facing this issue SAPSQL_ARRAY_INSERT_DUPREC dump while creating purchase order in transactions Me21n, Me59n, BAPI etc…

Solution and Reason: This is because when the system try to insert a new record into POEXT with empty EBELN field and already empty EBELN field(s) entry(s) existing in the table, SAPSQL_ARRAY_INSERT_DUPREC dump raised.

Follow the below solution which ever suits for your situation:

  • Implementation of note 1653977 prevents in the future that the table POEXT filled with empty EBELN entries.
  • Update: Implement also note 2020660 and Version 6 of note 2037157 to prevent above problem while changing / editing an existing document (e.g. adding new item into an existing PO)
  • Deletion of the POEXT entries with empty EBELN field is also required. To do this, you can use the attached report (Z_DELETE_EMPTYEBELN_FROM_POEXT) in the SAP note 2008784.
  • POEXT table’s task is to store enhancement fields for Purchase Order. Used for custom enhancements.
  • It is used for the integration of the MM-core with other applications like SCM, PSM, TM etc. in order to provide a standard PO with enhancement fields.
  • Similar update termination for POEXT when PO sent from SAP APO can be solved by note 1731050.

SAPSQL_ARRAY_INSERT_DUPREC or DBSQL_DUPLICATE_KEY_ERROR dump in transaction SU01

You may get the error while copying the user in SU01 transaction. Kindly check the dump where it is terminated, if you may see the termination occurred while accessing the table ESDUS then please refer below.

Reason and solution: The problem occurs due to inconsistencies in the table ESDUS caused in the deletion of users (Some data is not deleted). It is unknown how the table ESDUS becomes inconsistent, we can only repair it.

Execute the report ZDELESDUS from SAP note 197012.

This report can help in most of the cases, but has some restrictions: If the report does not help, please copy the user without the “Personalization data”. Then the dump will not occur and the user can be created. The personal data will be automatically created for the new user anyway during the usage of the system. Therefore creating the user without these does not mean any problems.

SAPSQL_ARRAY_INSERT_DUPREC in CAT2 or CATS_APPR_LITE

Sometimes you may get a same error in while using transactions CAT2 and CATS_APPR_LITE. Kindly check the dump in more detail, if you could see the termination program occurred with the table CATSCO then you can refer below scenarios to solve the problem.

The problem mostly because of inconsistencies in the CATSCO table in relation to the CATSDB, or because of the max number range is reached for creating IDs on the CATSDB.

In this case we have three possible solutions:

  • Report ZCATSR00 to remove inconsistencies: The note 304418 contains the report ZCATSR00.
  • Increase the number range: Compare the highest counter number of CATSDB table in SE16 with CATS table in SNRO transaction. The number range transactions are CAC7 or CAC8. Compare ranges and increase number range.
  • Refer the note 1368487 in situation: When the user modifies the approved record in CAT2 transaction, the cancellation records are not inserted into interface tables (PTEX2000 &PTEX2010) in case of HR transfers. This leads to wrong posting of records and creates inconsistency in database.
  • Apply the notes 1368487 and 1357066 in the situation: An Internal Error(LR030) occurs when user performs Approve->Reset Approval ->Rejects ->Withdraw Rejection on a timesheet entry record, in the report RCATS_APPROVE_ACTIVITIES (Transaction: CATS_APPR_LITE).

SAPSQL_ARRAY_INSERT_DUPREC dump in PPM tables during CIF integration

/SAPAPO/PPM_CLEAN_UP report to remove PPM data inconsistencies from database. But sometimes due to some inconsistencies the report left few entries in the system. Because of these inconsistent entries the error may arise. The PPM /SAPAPO/* tables are coherently linked and so many of /SAPAPO/* tables will be affected.

To avoid such errors situations the report /SAPAPO/PPM_CLEAN_UP should execute completely without any errors. If you encounter any error situation first apply the below notes and execute again.

1520005: Implement this code correction before running /SAPAPO/PPM_CLEAN_UP report in your SCM system.

1517463: Implement attached code correction and run /SAPAPO/PPM_CLEAN_UP report again. Obsolete entries from /SAPAPO/TRPROD should be deleted as well.

If you have lot of PPM data in the system then try executing this report in delta and in the background mode.

ME41: SAPSQL_ARRAY_INSERT_DUPREC dump when creating RFQ

The dump you may get when you create an RFQ by providing required data along with a vendor. Enter another vendor and change the material description in the item overview screen. Now when you hit enter, the system adds a duplicate item with material description from material master. On saving the RFQ, system results in dump SAPSQL_ARRAY_INSERT_DUPREC.

  • Please apply the note: 2013717 and 2182717

SAPSQL_ARRAY_INSERT_DUPREC (GL_RECID changed) while posting documents

You may face an error SAPSQL_ARRAY_INSERT_DUPREC When posting to a FI-SL line item table (for example, using trans. FB01, MR01, MB01 etc.), an update termination SAPSQL_ARRAY_INSERT_DUPREC occurs in program SAPLGIV1 (or also SAPLGIV2, SAPLGIV3, SAPLGIVA) in the form NEW_INSERT_ZZxxxA, where ZZxxxA is your FI-SL line item table.

This can have two causes:

  • The status of the number range object GL_RECID (FI-SL record numbers) has been reset (manually or by means of transport, possibly also by copying a client) without also resetting the corresponding transaction data.
  • The status of the number range object GL_BUCHEN (FI-SL document numbers for direct posting from other applications) has been reset.
  • It may also be that, after copying a client, the number range buffer of one of the two objects was not reset (this should really only be done by the system). In this case, start your local instance to synchronize the number range buffer.

Please check the two causes as follows:

Compare the status of the number range object GL_RECID (trans. SNRO) with the largest record number (field GL_SIRID) in your FI-SL line item table (you can determine the largest record number, for example, using transaction SE17 or by means of a single-line ABAP). If the current number level is smaller than the largest record number, increase it (to the largest record number).

Do the same for the number range object ‘GL_BUCHEN’ and the document number (DOCNR field)

SAPSQL_ARRAY_INSERT_DUPREC for NEW_INSERT_GLPCA

You may face the update termination SAPSQL_ARRAY_INSERT_DUPREC in program SAPLGIV1 (or SAPLGIV2, SAPLGIV3, SAPLGIVA as well) in Form NEW_INSERT_GLPCA When you post to the profit center line item table GLPCA using Transactions FB01, MR01, MB01 etc..

Reason and Prerequisites

The situation occurs generally always if number range objects and transaction data no longer match. The number range object thus delivers a number, which has already been used in the transaction data. The reasons for this are mostly of a technical nature, and cannot be found in the application. Possible causes are described in the related Note 62150.

In Profit Center Accounting, three number range objects exist, which are used for the actual data update.

  1. GL_RECID: this is a consecutive number for the unambiguous identification of a GLPCA record (GLPCA-GL_SIRID)
  2. COPCA_OBJ: delivers the document number (GLPCA-DOCNR) for documents which are copied from other applications (FI/CO/MM/SD)
  3. GL_LOCAL: delivers the document number (GLPCA-DOCNR) for documents which are created in Profit Center Accounting itself (PCA allocations, manual postings)

Note the following:

GL_RECID and GL_LOCAL are jointly used by special purpose ledgers (FI-SL) and Profit Center Accounting. In particular GL_RECID, thus should be modified manually only if object number inconsistencies or update terminations occur.

Solution

Transfer the attached report (in SAP Note 209141) from the advance corrections with the name ZPCA_CHECK_NUMBER_RANGE_OBJECT (Releases later than 4.0) or ZPCACNRO respectively (Releases earlier than 4.0) into your system.

Maintain the following selection texts:

COPCA_OB  COPCA_OBJ

GL_LOCAL   GL_LOCAL

GL_RECID  GL_RECID

RBUKRS    Company code

RYEAR      Fiscal year

Maintain the following text symbols:

010 No data

020 Object

030 GLPCA

040 Number range

050 Status

060 Messages

100 GLPCA contains no data

110 Error: NUMBER_GET_NEXT ! SY-SUBRC:

If you start the report now, the maximum entry is selected in table GLPCA (column GLPCA) for every number range object selected by you and compared with the next number from the number range object (column Number range). If the GLPCA entry is larger than the number range entry, you should raise the corresponding counter.

With a double-click on the respective line, you get directly to the maintenance of the number range object and can change there the counter reading. The new counter reading should be at least larger by 10 than the entry in the GLPCA column.

If you have to start the report due to the long runtime in the background, the direct navigation to the number range objects is not available to you. In this case, maintain the counter readings via Transaction SNUM.

If you have problems with the through-posting into PCA, you should start this report for the objects GL_SIRID and COPCA_OBJ. The analysis of GL_LOCAL is only necessary if you have problems with direct PCA postings (allocations, manual postings).

Kindly check the below notes as well.

62150  SAPSQL_ARRAY_INSERT_DUPREC in FI-SL table (line item)

491265            KB31N: SAPSQL_ARRAY_INSERT_DUPREC with table GLPCA

490147            SAPSQL_ARRAY_INSERT_DUPREC for online postings

485868            ALLOCATION: DUPREC error for plan assessment/distribution

395496            SAPSQL_ARRAY_INSERT_DUPREC with GLPCA

1156279          EC-PCA/FI-SL: SAPSQL_ARRAY_INSERT_DUPREC INS_GLPCA

 

4 Responses

  1. Ramesh says:

    Nice post on error sapsql_array_insert_duprec

  2. Ramesh says:

    Best article on sapsql_array_insert_duprec… saved lot of time… 🙂

  3. Kumar says:

    excellent, thanks for summarising all problems related to sapsql_array_insert_duprec

  4. Raghuchowdary says:

    Best article for sapsql and more nice explained.