SQL Creator/Executor using an ArcSDE connection works in FME Form but is unsuccessful when published to FME Flow

I generally will not execute SQL statements against the ArcGIS API .

ESRI has never really developed SQL support very well for its APIs, as it can only parse a small subset of SQL compliant syntax into equivalent SDE/GDB commands, so you are captive to whatever functionality and SQL support that the ESRI APIs have on the machine executing FME from since FME does not use its own Eg. SDE library/module but relies on separate machine configuration.  This makes SQL against ESRI APIs again more complex in needing yet another configuration dependency that has to be setup on the FME machine.

I suspect the message is effectively stating this separate configuration dependency issue: The Form machine has a different ArcGIS installation/config on it to what the Flow machine does, and so the Readers that search for, find and use these separate ArcGIS API files, will behave differently from FME machine to machine if there are difference by say putting ArcGIS Pro x.x on a Form machine, but maybe only ArcMap y.y on a Flow machine.

All our current use cases we execute SQL using the the SQL Server support embedded in FME for either SQL Server Non-Spatial or Spatial APIs/Readers.  This is aligned also with how ESRI views the world in suggesting to execute SQL using the database API rather than their own middleware, and give help and support on how to do this.

Noting that often….as per ESRI recommendations…..this is executed on the ESRI Versioned View for versioned data (and further noting that Versioned Views do support SQL Updates and Deletes).

We face the same problem while using a prefetch query within DatabaseJoiner, reading a ArcSDE Geodatabase.

Warning message on FME Flow (no data is read within DatabaseJoiner): Esri Geodatabase: ArcMap has limited SQL functionality and may not be able to process query ‘select * from xy where x = y’. Consider installing ArcGIS Pro for broader SQL functionality.

Altough the newest ArcGIS Pro version is installed on our FME Flow.

Any further suggestions on how to solve this? How do we if necessary have to adapt our workbench to perform the query directly on the database API for SDE databases?

What is the underlying database?  SQL Server?

If so, then what prevents using in DatabaseJoiner / SQLExecutor / SQLCreator:
“SELECT OBJECTID,… , SHAPE FROM XY WHERE ...”  against a SQL Server Spatial FME Format?

For example, here is basic Spatial SQL Query against the Versioned View in our own ArcGIS SDE SQL Server Host. (This particular table is Versioned where ArcCatalog will automatically create a Versioned View in the host Database to use in pure SQL queries that don’t use or need ArcGIS to query the data.  For Non-Versioned data alternatively can just refer to the Base Table)

It works just fine…….no ArcGIS on the FME machine needed at all, and so no extra ArcGIS configuration/setup needed.

Thank you a lot @bwn, this was exactly the decisive hint 🙂 We now used SQLExecutor instead, but it is basically the same.