UPDATE_INSERT Statement doesn't work

[Former Member]

Hi gurus,

i try to update a table on my MSSQL DB. I use "UPDATE_INSERT" action to insert or update the records. But i got following error message in RWB monitoring;

Message processing
failed. Cause:
com.sap.engine.interfaces.messaging.api.exception.MessagingException: Error
processing request in sax parser: Error when executing statement for
table/stored proc. 'BR_SALES' (structure 'STATEMENT'): com.microsoft.sqlserver.jdbc.SQLServerException:
Violation of UNIQUE KEY constraint 'UK_BR_SALES'. Cannot insert duplicate
key in object 'dbo.BR_SALES'. The duplicate key value is (0002000020, 2012-06-07).

Can anyone assist me?

Regards,

PM

[Former Member · Accepted solution]

Obviously you tried to insert a duplicate value on a field with unique constraint. You need to find out why.

CSY

[Former Member · Accepted solution]

Hi all,

after creating the index like OSS note -  i see the duplicate values on DB now. I need to UPDATE it and not create a duplicates.

ID            CUSTOMER    DATE          PRICE   AMOUNT     PRODUCT

444174    0002000020    2012-07-03    47.50     25.000     100000040

444175    0002000020    2012-07-03    47.50     25.000     100000040

444176    0002000020    2012-07-03    47.50     25.000     100000040

444177    0002000020    2012-07-03    47.50     25.000     100000040

444178    0002000020    2012-07-03    47.50     25.000     100000040

444179    0002000020    2012-07-03    47.50     25.000     100000040

444180    0002000020    2012-07-03    47.50     25.000     100000040

please advise.

PM

[zameerf · Accepted solution]

Hi Peter,

Can you confirm if you have the <Key> structure as well in your receiver JDBC structure at Mapping as per SAP Help?

The error is due to duplicate records and it should work with INSERT_UPDATE as action.

[baskar_gopalakrishnan2]

Insert_Update statement will not behave for sql server as that of other database like oracle. The solution to the problem is already specified by Inaki's SAP note. Take help from DB team. Basically you need to create unique index for the table BR_Sales with Ignore_Dub_key option.

[iaki_vila]

Hi Peter,

Check SAP Note 831162, the question 19. Using UPDATE_INSERT with MS SQL Server:java.sql.SQLException Can not insert duplicate key.

Regards.

[MichalKrawczyk]

Hi,

easy way:

1. use - logSQLStatement and set it to true in your JDBC channel to see the SQL statement

which you can use on the DB to see why it's not working

2. you can ask your DB admin to turn on the trace on DB to see why it's failing over there

3. maybe you're trying to insert the same record twice in your data set ?can you check that too?

Regards,

Michal Krawczyk