topic Re: SAPSQL_ARRAY_INSERT_DUPREC in Application Development and Automation Discussions

SAPSQL_ARRAY_INSERT_DUPREC

Former Member — Tue, 01 Sep 2009 21:52:13 GMT

Hello ABAP gurus,

I'm new to ABAP. Please kindly help me, I'm lost.

1. Using BDC I recorded tr. KO01 (Creation of Internal Order). I modified recorded source code to be able to do mass import of data from txt file (CALL FUNCTION 'WS_UPLOAD').

2. By running my program I created new Internal Orders (table COAS).

3. But some posted data were wrong (incorrect names of Internal Orders in txt file), so I wrote simple ABAP and deleted those wrongly posted records by statement:

DELETE FROM COAS WHERE ....

4. I corrected data in txt file and wanted to repeat step 2. But during executing I'm getting error:

SAPSQL_ARRAY_INSERT_DUPREC

CX_SY_OPEN_SQL_DB

I found here on the forum that it means, I'm trying to insert duplicate entries and there's primary key violation. What does it mean? I deleted those records from table in step 3... so how now they can be duplicate?

Please, advise how can I solve this problem..

Any help greatly appreciated.

Ondrej

Re: SAPSQL_ARRAY_INSERT_DUPREC

Clemenss — Tue, 01 Sep 2009 21:59:57 GMT

Hi Ondrej,

it is not recommended under any circumstances to change SAP standard tables by other means than SAP transactions. Usually not just one table is written to but several. Your random deletion of records caused a database inconsistency.

It will be difficult to get this repaired without qualified on-site help.

kind regards,

Clemens

Re: SAPSQL_ARRAY_INSERT_DUPREC

Former Member — Tue, 01 Sep 2009 22:01:39 GMT

By deleting entries from standard SAP table COAS, you may have created some data inconsistencies. There may have been other tables that had to be synchronized as well.

Hopefully, this was done in a development or test system and not production??

Rob

Re: SAPSQL_ARRAY_INSERT_DUPREC

luis_vesga — Tue, 01 Sep 2009 22:04:02 GMT

Is not recommended delete data from a standard SAP table. Maybe there exists another tables that are related to that table in the transaction and also must be deleted. In the source code detail of the short dump must be the table that the program is trying to update.

Re: SAPSQL_ARRAY_INSERT_DUPREC

Tamas_Hoznek — Tue, 01 Sep 2009 22:05:03 GMT

You deleted SAP standard table entries directly. That's one of the biggest no-nos you could do... now it looks like your number ranges are messed up.

Deletion of created documents (in this case, order headers) should never happen directly with a SQL statement like this. If you created orders via BDC, you will have a lot of other table entries (like items etc.) that are now orphans because you got rid of the document header.

Deletion should be done either by archiving or via an SAP-supplied function module for the given object.

Not sure what's the best way to get rid of the mess... you could correct the number range problem by changing the number range statuses in transaction SNRO, but this will be probably cumbersome.

Re: SAPSQL_ARRAY_INSERT_DUPREC

Former Member — Tue, 01 Sep 2009 22:06:48 GMT

Hi,

COAS is a view that has reference to the transparent table AUFK.

You never know which all tables are affected due to your BDC. Always the best practive is to use the standard tranasctions to delete any database entries.

Use standard transaction to delete the orders.

Regards,

Subramanian

Re: SAPSQL_ARRAY_INSERT_DUPREC

Former Member — Tue, 01 Sep 2009 22:24:23 GMT

Hi Luis, here is the source code from short dump. It seems the problem is when inserting into table JSTO. But I don't understand what this table is for and how to repair the problem :-((

30 LOOP AT jsto_upd WHERE chgkz = 'X'.

31 CHECK: jsto_upd-chgnr > '000',

32 jsto_upd-stsma NE jsto_upd-stsma_old.

33 CLEAR jcdo_tab.

34 MOVE-CORRESPONDING jsto_upd TO jcdo_tab.

35 MOVE-CORRESPONDING chdat TO jcdo_tab.

36 jcdo_tab-stsma_new = jsto_upd-stsma.

37 APPEND jcdo_tab.

38 ENDLOOP.

39 * Änderungen zu Einzelstatus (Insert)

40 LOOP AT jest_ins WHERE chgkz = 'X'.

41 CHECK jest_ins-chgnr > '000'.

42 CLEAR jcds_tab.

43 MOVE-CORRESPONDING jest_ins TO jcds_tab.

44 MOVE-CORRESPONDING chdat TO jcds_tab.

45 jcds_tab-chind = 'I'.

46 APPEND jcds_tab.

47 ENDLOOP.

48 * Änderungen zu Einzelstatus (Update)

49 LOOP AT jest_upd WHERE chgkz = 'X'.

50 CHECK jest_upd-chgnr > '000'.

51 CLEAR jcds_tab.

52 MOVE-CORRESPONDING jest_upd TO jcds_tab.

53 MOVE-CORRESPONDING chdat TO jcds_tab.

54 jcds_tab-chind = 'U'.

55 APPEND jcds_tab.

56 ENDLOOP.

57 * Verbuchung Statusobjekte

58 DESCRIBE TABLE jsto_ins LINES sy-tabix.

59 IF sy-tabix > 0.

>>>>> INSERT jsto CLIENT SPECIFIED

61 FROM TABLE jsto_ins.

62 ENDIF.

63 DESCRIBE TABLE jsto_upd LINES sy-tabix.

64 IF sy-tabix > 0.

65 field-symbols -OBJNR AND

79 STAT LIKE 'E%'.

Re: SAPSQL_ARRAY_INSERT_DUPREC

Tamas_Hoznek — Tue, 01 Sep 2009 22:27:42 GMT

JSTO is for status information; it is updated when you created those orders.

As you can see, there are a lot of tables that are impacted... sorry to say, but it may be time to find out what's the latest backup available and talk to the basis team about restoring the system. I don't think there is an easy way to fix this otherwise.

Re: SAPSQL_ARRAY_INSERT_DUPREC

Former Member — Wed, 02 Sep 2009 13:03:50 GMT

I'd like to know if this was done in a sandbox, development, testing or production environment. What you will do is dependent on this.

Rob

Re: SAPSQL_ARRAY_INSERT_DUPREC

Former Member — Wed, 02 Sep 2009 13:05:50 GMT

Hi Rob, unfortunately on production system.

Re: SAPSQL_ARRAY_INSERT_DUPREC

Former Member — Wed, 02 Sep 2009 13:17:38 GMT

Ouch - can users create orders using transaction KO01 or do they get the same error?

Rob

Re: SAPSQL_ARRAY_INSERT_DUPREC

Former Member — Wed, 02 Sep 2009 13:32:06 GMT

Today the situation seems relatively OK - it is possible to create Internal Orders in KO01 normally. We have more Order Types and number ranges. That BDC import (and direct delete from table) which I did yesterday was done completely in new number range with new Order Type. So, we just changed Order Type and number range in imported data to different new values and luckily everything works alright. The only little problem seems to be we can't use that original Order Type again never in the future, because of error SAPSQL_ARRAY_INSERT_DUPREC.

So it seems that afterall the problem is relatively small - but now I know I will never delete records from standard table directly again.

Re: SAPSQL_ARRAY_INSERT_DUPREC

Former Member — Wed, 02 Sep 2009 13:40:41 GMT

Well, you may encounter problems down the road. I would try to duplicate the problem in a sandbox environment and use transaction ST05 to see which tables are hit.

Rob

Re: SAPSQL_ARRAY_INSERT_DUPREC

Former Member — Wed, 02 Sep 2009 13:47:26 GMT

I tried it yesterday, but I'm begginner, so I wasn't able to understand it too well...

I don't understand what are the values 4A8F1F96FD6C1287E1000000AC141706 which are inserted to those tables on the left.

VBMOD REEXEC 1 0 INSERT VALUES( '4A8F1F96FD6C1287E1000000AC141706' , 1 , 'STATUS_UPDATE' , '1' , 255 )

VBDATA REEXEC 1 0 INSERT VALUES( '4A8F1F96FD6C1287E1000000AC141706' , 1 , 0 , 242 , <LRAW> )

VBMOD REEXEC 1 0 INSERT VALUES( '4A8F1F96FD6C1287E1000000AC141706' , 2 , 'OBJECT_NUMBER_INSERT_GENERIC' , '1' , 255 )

VBDATA REEXEC 1 0 INSERT VALUES( '4A8F1F96FD6C1287E1000000AC141706' , 2 , 0 , 82 , <LRAW> )

VBMOD REEXEC 1 0 INSERT VALUES( '4A8F1F96FD6C1287E1000000AC141706' , 3 , 'K_SRULE_SAVE_UTASK' , '1' , 255 )

VBDATA REEXEC 1 0 INSERT VALUES( '4A8F1F96FD6C1287E1000000AC141706' , 3 , 0 , 330 , <LRAW> )

VBMOD REEXEC 1 0 INSERT VALUES( '4A8F1F96FD6C1287E1000000AC141706' , 4 , 'RK_AUFTRAG_SICHERN' , '1' , 255 )

VBDATA REEXEC 1 0 INSERT VALUES( '4A8F1F96FD6C1287E1000000AC141706' , 4 , 0 , 443 , <LRAW> )

VBHDR REEXEC 1 0 INSERT VALUES( '4A8F1F96FD6C1287E1000000AC141706' , '010' , 'HLADEK' , ' ' , 'SAPMKAUF' , 'KO01' , '20090902031740105033000600sapde

EXECSTA 0 0 COMMIT WORK ON CONNECTION 0

Re: SAPSQL_ARRAY_INSERT_DUPREC

Tamas_Hoznek — Wed, 02 Sep 2009 13:57:58 GMT

VBHDR, VBDATA and VBMOD are tables used by the update process - basicaly, they facilitate the update of all tables the application wants to change. You need not worry about that, as it is a part of the technical solution how the system handles update requests. Basically, VBHDR is the header for an update request, VBDATA will hold the data necessary for DB updates and VBMOD will tell the update process what function modules to use.

There should be other tables in the trace that are updated, look for those.

As for not having any problems today, that's good, but as Rob pointed out, you may still encounter problems later on because there is definitely an inconsistency now. If you are lucky, then you only have a bunch of orphan records in a number of tables that don't belong to any documents now since the order headers were deleted. But it's hard to say whether there will be some other problem because of this.

Keep your fingers crossed... hopefully you'll be fine

Re: SAPSQL_ARRAY_INSERT_DUPREC

Former Member — Wed, 02 Sep 2009 14:21:43 GMT

Tamas, thank you for explanation, for your time and support - and to all others too...

Yes, there are lots of other tables in the trace,

NRIV

TFAWC

TKO03

DDNTT

DD07L

DD07T

DD07V

DD07L

DD07T

DD07V

ANIA

CEPC

CEPC_BU

COAS

IMAKZ

D020S

D021T

but only with REOPEN and FETCH operation and SELECT statement..

With INSERT are in the trace just those tables in my previous post...

Re: SAPSQL_ARRAY_INSERT_DUPREC

Former Member — Wed, 02 Sep 2009 14:29:33 GMT

Better check again. I think other tables are updated as well.

Rob

Re: SAPSQL_ARRAY_INSERT_DUPREC

luis_vesga — Wed, 02 Sep 2009 19:58:41 GMT

The table JSTO contains the information about the status of the order. Usually the OBJNR is obtaining concatenating the word OR+the order number. But it will be difficult to resolve this. If you are using external numbering for the orders just use another number and try to load the orders.