Hi,
I have developed the pipeline which reads the data from the incoming stream and send to SQL Merge. My pipeline looks like below.
My Input JSON is as follows:
[ {
โmember_idโ: 13000561,
โintervention_idโ: 1000034495,
โsentimentโ: null,
โlast_updatedโ: null,
โincentive_offeredโ: null,
โreaction_detailsโ: null,
โdashboard_nameโ: โSalesForceโ,
โcreatedโ: null,
โintervention_assessmentโ: null,
โtactic_detailsโ: null,
โcreated_byโ: null,
โfollowup_deadlineโ: {
โ_snaptype_localdateโ: โ2019-10-13โ
},
โfollowupโ: false,
โlast_updated_byโ: null,
โcontact_methodโ: null,
โcontact_madeโ: false,
โdate_of_interventionโ: null,
โintervention_notesโ: null
}]
Configuration for SQL Merge Snap:
Please note that we are allowing null for all the columns expect the intervention id which is the primary key. When we are running the pipeline, we are getting the following error.
{
โerrorโ: โSQL operation failedโ,
โstacktraceโ: โjava.sql.BatchUpdateException: Implicit conversion from data type varbinary to date is not allowed. Use the CONVERT function to run this query.\n\tat com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(SQLServerPreparedStatement.java:1178)\n\tat com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:128)\n\tat com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeBatch(HikariProxyPreparedStatement.java)\n\tat org.jooq.tools.jdbc.DefaultStatement.executeBatch(DefaultStatement.java:93)\n\tat org.jooq.impl.BatchSingle.executePrepared(BatchSingle.java:239)\n\tat org.jooq.impl.BatchSingle.execute(BatchSingle.java:182)\n\tat com.snaplogic.snap.api.sql.operations.JdbcOperationsImpl.lambda$flushBatch$8(JdbcOperationsImpl.java:1547)\n\tat com.snaplogic.snap.api.sql.operations.JdbcOperationsImpl$$Lambda$394/1810556666.call(Unknown Source)\n\tat net.jodah.failsafe.Functions$11.call(Functions.java:263)\n\tat net.jodah.failsafe.SyncFailsafe.call(SyncFailsafe.java:145)\n\tat net.jodah.failsafe.SyncFailsafe.get(SyncFailsafe.java:69)\n\tat com.snaplogic.snap.api.sql.operations.JdbcOperationsImpl.flushBatch(JdbcOperationsImpl.java:1546)\n\tat com.snaplogic.snaps.sql.SimpleSqlWriteSnap.handle(SimpleSqlWriteSnap.java:136)\n\tat com.snaplogic.cc.snap.common.SnapRunnableImpl.callback(SnapRunnableImpl.java:836)\n\tat com.snaplogic.cc.snap.common.SnapRunnableImpl.executeForSuggest(SnapRunnableImpl.java:644)\n\tat com.snaplogic.cc.snap.common.SnapRunnableImpl.doRun(SnapRunnableImpl.java:852)\n\tat com.snaplogic.cc.snap.common.SnapRunnableImpl.access$000(SnapRunnableImpl.java:118)\n\tat com.snaplogic.cc.snap.common.SnapRunnableImpl$1.run(SnapRunnableImpl.java:392)\n\tat com.snaplogic.cc.snap.common.SnapRunnableImpl$1.run(SnapRunnableImpl.java:388)\n\tat java.security.AccessController.doPrivileged(Native Method)\n\tat javax.security.auth.Subject.doAs(Subject.java:422)\n\tat org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1657)\n\tat com.snaplogic.cc.snap.common.SnapRunnableImpl.call(SnapRunnableImpl.java:387)\n\tat com.snaplogic.cc.snap.common.SnapRunnableImpl.call(SnapRunnableImpl.java:118)\n\tat java.util.concurrent.FutureTask.run(FutureTask.java:266)\n\tat java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)\n\tat java.util.concurrent.FutureTask.run(FutureTask.java:266)\n\tat java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)\n\tat java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)\n\tat java.lang.Thread.run(Thread.java:745)\nโ,
โreasonโ: โImplicit conversion from data type varbinary to date is not allowed. Use the CONVERT function to run this query., error code: 257, SQL state: S0003โ,
โresolutionโ: โPlease check for valid Snap properties and input data.โ,
โstatus_codeโ: โerrorโ,
โstatusโ: -3,
โsnap_detailsโ: {
โlabelโ: โSQL Server - Mergeโ,
โinstance_idโ: โ7de1aaf6-483b-4e42-b1ed-46cf7f1fd2bdโ,
โclass_idโ: โcom-snaplogic-snaps-sqlserver-mergeโ,
โbuild_tagโ: โsnapsmrc523โ,
โviewsโ: {
โinโ: {
โinput0โ: {
โcountโ: 2
}
},
โoutโ: {},
โerrorโ: {
โerror0โ: {
โcountโ: 0
}
}
}
},
โoriginalโ: {
โmember_idโ: 13000561,
โintervention_idโ: 1000034495,
โsentimentโ: null,
โlast_updatedโ: null,
โincentive_offeredโ: null,
โreaction_detailsโ: null,
โdashboard_nameโ: โSalesForceโ,
โcreatedโ: null,
โintervention_assessmentโ: null,
โtactic_detailsโ: null,
โcreated_byโ: null,
โfollowup_deadlineโ: {
โ_snaptype_localdateโ: โ2019-10-13โ
},
โfollowupโ: false,
โlast_updated_byโ: null,
โcontact_methodโ: null,
โcontact_madeโ: false,
โdate_of_interventionโ: null,
โintervention_notesโ: null
}
}
Please note that same thing works with the SQL Insert, but we are expecting the upsert to the table. Please help us resolving the issues.
Thanks,
Gowdhaman
