You can translate the question and the replies:

Updates failing in scheduler

I have designed a VDP job in schdeuler. I have two three columns in the MSSQL table and all three of them are part of primary column. In the exporter section of scheduler job I have set the config as below Delete table contents to false update tuple if exists to true When I execute the job, it is throwing error as below. com.denodo.scheduler.core.exporter.jdbc.JDBCExporter [job_name] - SQLState: S1000 49974031 ERROR 2019-11-22T00:32:58.713 com.denodo.scheduler.core.exporter.jdbc.JDBCExporter [[job_name.pool-4-thread-7]] - Error updating document: {LSL_OBJECT_ID=0bd4e4ab-394e-4291-adc4-ff6eed1dee9f, LSL_TITLE=Task Specific, CULTURE_ID=20, _$DOC_ID=403140} java.sql.SQLException: Violation of PRIMARY KEY constraint 'PK_pkname'. Cannot insert duplicate key in object 'dbo.table_name'. The duplicate key value is (0bd4e4ab-394e-4291-adc4-ff6eed1dee9f, Task Specific, 20). at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372) ~[jtds.jar:1.3.1] at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2988) ~[jtds.jar:1.3.1] at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2421) ~[jtds.jar:1.3.1] at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:671) ~[jtds.jar:1.3.1] at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:613) ~[jtds.jar:1.3.1] at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:572) ~[jtds.jar:1.3.1] at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeUpdate(JtdsPreparedStatement.java:727) ~[jtds.jar:1.3.1] at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) ~[commons-dbcp.jar:1.4] at com.denodo.scheduler.core.exporter.jdbc.JDBCExporter.toJDBC(Unknown Source) ~[denodo-scheduler-core.jar:7.0.4] at com.denodo.scheduler.core.exporter.jdbc.JDBCExporter.normalExport(Unknown Source) [denodo-scheduler-core.jar:7.0.4] at com.denodo.scheduler.core.exporter.jdbc.JDBCExporter.export(Unknown Source) [denodo-scheduler-core.jar:7.0.4] at com.denodo.scheduler.core.job.ExportationJob.exportDocuments(Unknown Source) [denodo-scheduler-core.jar:7.0.4] at com.denodo.scheduler.core.job.ExportationJob.doFlushQueuedDocuments(Unknown Source) [denodo-scheduler-core.jar:7.0.4] at com.denodo.scheduler.core.job.AbstractDatabaseExtractionJob.flushQueuedDocuments(Unknown Source) [denodo-scheduler-core.jar:7.0.4] As per the configuration and understanding, tuple should get updated when there is a duplicate records. Another observation is other tables where all columns are not part of primary key, they are getting updated. Please advise how to fix this issue
user
02-12-2019 05:41:35 -0500

2 Answers

Hi, I was able to reproduce the error. The reason for this error is that it is not possible to execute an UPDATE query on a tuple in a target table where the primary key is comprised of all the columns. The purpose of “Update tuple if entry already exists” is to update the columns not part of the primary key for the row matching the primary key. In your case, all of the columns make up the primary key, so there are no non-primary key columns to update. For this scenario, I would uncheck “Update tuple if entry already exists in DB” since the exported results will also be the same. You can read [Postprocessing Section (Exporters)](https://community.denodo.com/docs/html/browse/latest/scheduler/administration/creating_and_scheduling_jobs/configuring_new_jobs/postprocessing_section_exporters) in the Scheduler Administration Guide for more information on this topic. Hope this helps!
Denodo Team
05-12-2019 12:18:05 -0500
Thank you for the clarification. It was helpful. Regards
user
06-12-2019 02:51:05 -0500
You must sign in to add an answer. If you do not have an account, you can register here