SQL Server Merge Snap - Issues with Date as Null

Hi,

I have developed the pipeline which reads the data from the incoming stream and send to SQL Merge. My pipeline looks like below.

image

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

Seems like snaptype_localdate in your input is going as string, but the DB is expecting a date field, can you try to convert this to date and then pass it in the input to the DB.

Hi,

I have tried to cast as date type. But it still could not go with SQL merge, but note that it is working with SQL Inserts.

Thanks,
Gowdhaman