Skip to main content
Solved

Why does ORDER BY appear incompatible with Microsoft SQL Server Non-Spatial (ADO) ?

  • September 15, 2023
  • 2 replies
  • 282 views
raymondbrunner
Contributor
Forum|alt.badge.img+11

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?

Best answer by ctredinnick

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

View original
Did this help you find an answer to your question?

2 replies

ctredinnick
Supporter
Forum|alt.badge.img+18
  • ctredinnickSupporter
  • Supporter
  • Best Answer
  • September 16, 2023

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

raymondbrunner
Contributor
Forum|alt.badge.img+11
  • raymondbrunnerContributor
  • Author
  • Contributor
  • September 19, 2023

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


Helpful Members This Week

Community Stats

31,237
Posts
118,442
Replies
38,833
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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.