Error when reading from SQLMX database via JDBC connector

[feenst]

I am working on a pipeline to pull data from a SQL Server database and insert into a SQLMX database.

I got this pipeline working, but it fails after the first run as it’s trying to insert records that already exist.

My thought was to use a pipeline like the following:

However, the Generic JDBC select from the SQLMX database fails with the following error:

Failure: Failed to execute select query., Reason: SQL [select * from "DEFAULT_SCHEMA"."ASSETS" limit ?]; *** ERROR[3128] LIMIT is a reserved word. It must be delimited by double-quotes to be used as an identifier. [2017-09-01 16:42:39], Resolution: Please check sql query.

I have nothing included in the limit clauses.

I am able to use the Generic JDBC - Execute snap to read from that database / table, but the snap has no head that I can connect with other snaps.

[akidave]

SQL/MX does not seem to support the LIMIT clause. You can use the Generic JDBC Execute snap, add an output view to be able to link it to downstream snaps.

[feenst]

Thanks @akidave for the tip on the output view. That worked in this scenario.
However, I don’t have any of the “limit” fields included in my UI so I’m not sure why the limit clause would be included in the query.

[akidave]

Did you get the LIMIT error during pipeline save? The preview data generation automatically adds a LIMIT to the SQL query in the SELECT snap. The LIMIT is not added during pipeline execution.

[feenst]

Yes, it seems to only happen during save.

Not sure why the SQLMX fails though when the SQL Server (via JDBC as well) succeeds as I can’t use a LIMIT clause when querying the SQL Server database directly …