MS-SQL Server Stored Procedures

[brianc0755]

I have a pipeline that creates a staging table in a SQL database that is then compared to a target table using a SQL server stored procedure. It is important that the staging table be completely populated before the stored procedure is executed, and there is a gate after the staging table insert and before the stored procedure call.

Five percent of the time the pipeline works as expected. All other times the staging table insert hangs when about half the records are loaded.

The pipeline does not report an error but the jcc error log has a line (see below) that seems to be relevant.

{“ts”:“2023-05-16T14:50:39.197Z”,“lvl”:“ERROR”,“fi”:“SnapRunnableImpl.java:1034”,“msg”:“Snap execution failed unexpectedly”,“exc”:“com.snaplogic.api.ExecutionException: Batch operation failed\r\n\tat com.snaplogic.snap.api.ErrorViewsImpl.write(ErrorViewsImpl.java:264)\r\n\tat com.snaplogic.snaps.sql.SimpleSqlSnap.process(SimpleSqlSnap.java:496)\r\n\tat com.snaplogic.snap.api.ExecutionUtil.process(ExecutionUtil.java:106)\r\n\tat com.snaplogic.snap.api.ExecutionUtil.executeForDocument(ExecutionUtil.java:118)\r\n\tat com.snaplogic.snap.api.ExecutionUtil.execute(ExecutionUtil.java:81)\r\n\tat com.snaplogic.snap.api.SimpleSnap.execute(SimpleSnap.java:70)\r\n\tat com.snaplogic.cc.snap.common.SnapRunnableImpl.executeSnap(SnapRunnableImpl.java:812)\r\n\tat com.snaplogic.cc.snap.common.SnapRunnableImpl.execute(SnapRunnableImpl.java:586)\r\n\tat com.snaplogic.cc.snap.common.SnapRunnableImpl.doRun(SnapRunnableImpl.java:877)\r\n\tat com.snaplogic.cc.snap.common.SnapRunnableImpl.call(SnapRunnableImpl.java:436)\r\n\tat com.snaplogic.cc.snap.common.SnapRunnableImpl.call(SnapRunnableImpl.java:120)\r\n\tat java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)\r\n\tat java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)\r\n\tat java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)\r\n\tat java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)\r\n\tat java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)\r\n\tat java.base/java.lang.Thread.run(Thread.java:834)\r\nCaused by: com.snaplogic.snap.api.SnapDataException: Batch operation failed\r\n\tat com.snaplogic.snaps.sql.SimpleSqlSnap.process(SimpleSqlSnap.java:493)\r\n\t… 15 more\r\nCaused by: com.snaplogic.api.ExecutionException: Batch operation failed\r\n\tat com.snaplogic.snap.api.ErrorViewsImpl.write(ErrorViewsImpl.java:264)\r\n\tat com.snaplogic.snap.api.sql.DatabaseUtils.writeErrorView(DatabaseUtils.java:933)\r\n\tat com.snaplogic.snap.api.sql.operations.JdbcOperationsImpl.insert(JdbcOperationsImpl.java:1009)\r\n\tat com.snaplogic.snaps.sql.SimpleSqlInsertSnap.executeInsert(SimpleSqlInsertSnap.java:209)\r\n\tat com.snaplogic.snaps.sqlserver.Insert.processData(Insert.java:91)\r\n\tat com.snaplogic.snaps.sql.SimpleSqlWriteSnap.processDocument(SimpleSqlWriteSnap.java:202)\r\n\tat com.snaplogic.snaps.sql.SimpleSqlSnap.process(SimpleSqlSnap.java:484)\r\n\t… 15 more\r\nCaused by: com.snaplogic.snap.api.SnapDataException: Batch operation failed\r\n\tat com.snaplogic.snap.api.sql.DatabaseUtils.writeErrorView(DatabaseUtils.java:926)\r\n\t… 20 more\r\nCaused by: net.jodah.failsafe.FailsafeException: java.lang.InterruptedException: sleep interrupted\r\n\tat net.jodah.failsafe.SyncFailsafe.call(SyncFailsafe.java:165)\r\n\tat net.jodah.failsafe.SyncFailsafe.get(SyncFailsafe.java:69)\r\n\tat com.snaplogic.snap.api.sql.operations.JdbcOperationsImpl.flushBatch(JdbcOperationsImpl.java:1943)\r\n\tat com.snaplogic.snap.api.sql.operations.JdbcOperationsImpl.insert(JdbcOperationsImpl.java:1005)\r\n\t… 19 more\r\nCaused by: java.lang.InterruptedException: sleep interrupted\r\n\tat java.base/java.lang.Thread.sleep(Native Method)\r\n\tat net.jodah.failsafe.SyncFailsafe.call(SyncFailsafe.java:162)\r\n\t… 22 more\r\n”,“snlb”:“Encrypted+cdiwork±+insert”,“snrd”:“fd47ae71-0f96-468d-b307-aec4d2b336c0”,“plrd”:“5dba84c8017c0e2416c17c86_85c2f064-2c91-46b7-bf78-769511a1bd5a”,“prc”:“Encrypted cdiwork - insert[5dba84c8017c0e2416c17c86_85c2f064-2c91-46b7-bf78-769511a1bd5a – fd47ae71-0f96-468d-b307-aec4d2b336c0]”,“xid”:“cf1027ce-b460-de45-0974-1f51a2a727f4”}