UPDATE_INSERT Error java.sql.SQLException "Cannot insert duplicate key"

[ng82si]

Hi Experts,

I have proxy-->JDBC Scenario by using Update_Insert statement.

Sometimes I got the following error:

Error during JDBC-Message-Processing; Reason: Error processing request in sax parser: Error when executing statement for table/stored proc. 'DISPO_Unit' (structure 'Statement'): com.microsoft.sqlserver.jdbc.SQLServerException: ... cannot insert duplicate key. The double key value is (143801).

I searched and get the following SAP-Note 831162 Q & A 19:

19. Using UPDATE_INSERT with MS SQL Server:java.sql.SQLException Cannot insert duplicate key

• Q: When I construct payload using UPDATE_INSERT, I get Cannot insert duplicate key in object XXX. How do I resolve this?

           A: You need to create UNIQUE INDEX for the table with 'IGNORE_DUP_KEY' option.
           For instance, if the table name is TEST and its primary key is id, then following statement may be executed using Query analyzer tool.
           CREATE UNIQUE INDEX TEST_INDEX ON TEST(id) WITH IGNORE_DUP_KEY
           After executing the above command, please re-run the scenario.

Could anyone explain how to create the unique index?

Thanks a lot!

Regards

Christine

[Former Member]

Hi Christine, in your PI message structure did you define the key fields to match how the database was built?

You may have something like:

[Your_Message_Type]

     Statement

          dbTableName

               @action=UPDATE_INSERT

               table

               access

                    [lots of fields you have mapped]

               key

                    [primary keys of your destination table should go here]

This way PI knows whether to insert, or update the record if it is already existing.

This works well when inserting / updating directly to a table.

I've never tried this against a stored procedure so using the two methods may not be compatible.