Skip to main content
  • EN

We had numerous SQL Creators based on JDBC connections that worked in both FME Desktop and FME Server.

 

We are in the process of migrating our SQL Creators from JDBC to Microsoft SQL Server Non-Spatial (ADO). In doing so their SQL continues to perform well in Form, but now generates Warnings when executed on Flow.

 

The Warning takes the following form:

"Microsoft SQL Server Non-Spatial Reader: Getting Custom SQL schema failed. Custom SQL query 'SELECT ... WHERE ... ORDER BY ...' may execute in an unexpected manner. Make sure all columns are named (any ambiguous columns, e.g. GEOM.STBuffer(1), must have an AS clause). If there is an ORDER BY clause, either remove it or add a TOP clause."

 

Removing the ORDER BY does eliminate the Warnings generated on Flow.

 

However we enjoyed using the power of the database to presort the records prior to being transformed within the workspace.

 

Why is ORDER BY incompatible with Microsoft SQL Server Non-Spatial (ADO), and what other alternatives are there?

It's because FME is trying to use your statement to query what schema it's going to get, and when it does this, it gets this error from the database "The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified." - but this doesn't prevent the actual selection of data that happens next so it's just a warning and not an error.

One option is to change the SQLCreator from "schema and data features" to output "data features".

You can also (and I do this a bit) write the SQL statement as SELECT TOP 100 PERCENT

Thank you, ctredinnick.

 

I was not thinking of my SQL SELECT as being a View or Subquery. As a result, the use of a SELECT TOP clause did not make sense to me until I received your explanation.

 

Thank you, again!

Reply


Community Stats

30,108
Posts
114,295
Replies
37,745
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.