SQL Server JDBC Multitable throws com.microsoft.sqlserver.jdbc.SQLServerException for tablenames created with reserve keywords

Product: Streamsets Data Collector All Versions

Issue:

If a tablename is a reserve keyword in SQL Server the JDBC Multitable throws an exception as listed below

2020-04-28 13:44:57,634 [user:*admin] [pipeline:JDBC_SQLSS/JDBCSQLSS0f0302d2-cf06-4ed7-9eae-76213814e603] [runner:] [thread:Table Jdbc Runner - 0] [stage:JDBCMultitableConsumer_01] ERROR JdbcBaseRunnable - Error happened
java.util.concurrent.ExecutionException: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'USER'.
 at com.google.common.util.concurrent.AbstractFuture.getDoneValue(AbstractFuture.java:476)
 at com.google.common.util.concurrent.AbstractFuture.get(AbstractFuture.java:435)
 at com.google.common.util.concurrent.AbstractFuture$TrustedFuture.get(AbstractFuture.java:79)
 at com.google.common.util.concurrent.Uninterruptibles.getUninterruptibly(Uninterruptibles.java:143)
 at com.google.common.cache.LocalCache$Segment.getAndRecordStats(LocalCache.java:2352)
 at com.google.common.cache.LocalCache$Segment.loadSync(LocalCache.java:2324)
 at com.google.common.cache.LocalCache$Segment.lockedGetOrLoad(LocalCache.java:2286)
 at com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2201)
 at com.google.common.cache.LocalCache.get(LocalCache.java:3953)
 at com.google.common.cache.LocalCache.getOrLoad(LocalCache.java:3957)
 at com.google.common.cache.LocalCache$LocalLoadingCache.get(LocalCache.java:4875)
 at com.streamsets.pipeline.lib.jdbc.multithread.JdbcBaseRunnable.getOrLoadTableReadContext(JdbcBaseRunnable.java:513)
 at com.streamsets.pipeline.lib.jdbc.multithread.JdbcBaseRunnable.generateBatchAndCommitOffset(JdbcBaseRunnable.java:257)
 at com.streamsets.pipeline.lib.jdbc.multithread.JdbcBaseRunnable.run(JdbcBaseRunnable.java:178)
 at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
 at java.util.concurrent.FutureTask.run(FutureTask.java:266)
 at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
 at java.util.concurrent.FutureTask.run(FutureTask.java:266)
 at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
 at com.streamsets.pipeline.lib.executor.SafeScheduledExecutorService$SafeCallable.lambda$call$0(SafeScheduledExecutorService.java:226)
 at com.streamsets.datacollector.security.GroupsInScope.execute(GroupsInScope.java:33)
 at com.streamsets.pipeline.lib.executor.SafeScheduledExecutorService$SafeCallable.call(SafeScheduledExecutorService.java:222)
 at com.streamsets.pipeline.lib.executor.SafeScheduledExecutorService$SafeRunnable.run(SafeScheduledExecutorService.java:188)
 at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
 at com.streamsets.pipeline.lib.executor.SafeScheduledExecutorService$SafeCallable.lambda$call$0(SafeScheduledExecutorService.java:226)
 at com.streamsets.datacollector.security.GroupsInScope.execute(GroupsInScope.java:33)
 at com.streamsets.pipeline.lib.executor.SafeScheduledExecutorService$SafeCallable.call(SafeScheduledExecutorService.java:222)
 at com.streamsets.pipeline.lib.executor.SafeScheduledExecutorService$SafeRunnable.run(SafeScheduledExecutorService.java:188)
 at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
 at java.util.concurrent.FutureTask.run(FutureTask.java:266)
 at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
 at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
 at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
 at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
 at java.lang.Thread.run(Thread.java:748)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'USER'.
 at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
 at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632)
 at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:600)
 at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:522)
 at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7225)
 at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3053)
 at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:247)
 at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:222)
 at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:444)
 at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
 at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
 at com.streamsets.pipeline.lib.jdbc.multithread.TableReadContext.<init>(TableReadContext.java:77)
 at com.streamsets.pipeline.lib.jdbc.multithread.cache.JdbcTableReadContextLoader.load(JdbcTableReadContextLoader.java:90)
 at com.streamsets.pipeline.lib.jdbc.multithread.cache.JdbcTableReadContextLoader.load(JdbcTableReadContextLoader.java:40)
 at com.google.common.cache.LocalCache$LoadingValueReference.loadFuture(LocalCache.java:3542)
 at com.google.common.cache.LocalCache$Segment.loadSync(LocalCache.java:2323)
 ... 29 more

Versions affected:

All SDC Versions JDBC Multitable Stage

Solution:

Please set the below configuration and retry the pipeline.

JDBC Multitable -> Tables -> Table Name Pattern -> USER

JDBC Multitable origin > JDBC -> Quote Character -> DOUBLE QUOTES