Skip to main content
  • EN

I have two SQL Server spatial tables (one containing current records and one containing archived records) that I need to merge and read the features ordered by their version number in order to do onward processing in FME. 

 

I have been trying to use a SQLCreator to read these records into FME in the correct order using SQL similar to the below: 

Select a.* from
(
        SELECT
		  		  ,ttoid_version]
		  ,ÂSHAPE]

		FROM /dbo].Âa_boundaryline]
 
        union all
 
        SELECT 
		  ofid]
		  ,/toid_version]
		  , SHAPE]
		              
        FROM  dbo].>boundaryline]
 
) as a
order by a.fid, a.toid_version

This SQL works in SQL Server management studio without issue. However, when I use it in a SQLCreator (or SQLExecutor) I get the following warning(s) in the log and the geometry is not read (i.e. features have no geometry):

 

---

Microsoft SQL Server Spatial Reader: Query failed, possibly due to a malformed statement. Query Text `SELECT * INTO #fme_tempAdoDataTypeTable_spatialReader FROM ( Select a.* from(

 SELECT

  fid]

 ,itoid_version]

 ,tSHAPE]

 

 FROM #dbo].ea_boundaryline]

 union all

 SELECT 

  fid]

 ,Âtoid_version]

 ,vSHAPE]

 

 FROM >dbo].

) as a

where a.fid = 'osgb1000000522000310'

order by a.fid, a.toid_version ) AS customQuery WHERE 1=0'. Provider error `(-2147217900) 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.'

Microsoft SQL Server Spatial Reader: Getting Custom SQL schema failed. 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

Microsoft SQL Server Spatial Reader: Table `___FME_Custom_SQL_Statement___' does not include a spatial column. Geometry will not be read

Microsoft SQL Server Spatial Reader: Database read complete. Retrieved 3 feature(s)

---

 

If I remove the order by from the SQL, there is no warning and the geometry is read correctly. 

 

Looking at the error, it appears that FME is wrapping my query SQL with additional SQL and this is causing the issue with order by.  

 

I have also tried a Common Table Expression using WITH in an attempt to work around the issue. This executes with no warnings, but again results in no geometry being read. 

 

I am aware that FME has a sorter transformer, but this would not be practical here as the data volumes are large (>100m records in some cases), hence the need to use order by to get the database to do the sorting. 

 

Has anyone seen this behaviour before with SQLCreator/Executor and SQL Server? 

 

I'm fairly sure I have used similar SQL in the past with older versions of FME and have not had this issue.

 

Any ideas for a workaround that doesn't involve sorting inside FME?

 

 

Finally for Safe website bods, I'd just like to add that the form to create a post on this site is really poor. Very little space to type and options for text formatting are poor compared to similar forums elsewhere. Definitely requires improvement!

One workaround is to have a TOP clause like it says, Even if it's SELECT TOP (100000000) * it will work.

It might also be use of the * in the select statement. Looks like the error is raised when FME is selecting the data into a temp table to get what the schema is that it's going to read.

It's not desirable in this case because of the data volume, but you can also use a non-spatial reader, select the SHAPE column, then use that in a GeometryReplacer (MS SQL) to set the geometry.

Alternatively you could create a view on the database of the two tables unioned and sorted.

FME tries to deduce the schema of query results, but in some cases it doesn't quite get it, which is what you're seeing in the log. You can help FME detect the schema by using a TOP clause, as mentioned:

    Select TOP (100) PERCENT  a.* from
    (
            SELECT
    		      		  ,>toid_version]
    		  ,CSHAPE]
    		
    		FROM  dbo].	a_boundaryline]
     
            union all
     
            SELECT 
    		  ifid]
    		  ,    		  , SHAPE]
    		              
            FROM  dbo].Tboundaryline]
     
    ) as a
    order by a.fid, a.toid_version

 

Thanks @david_r​ and @ctredinnick​ - the TOP workaround works fine and it's also good to know about the option to read with the non-spatial reader and then use the GeometryReplacer as that might be useful in the future.

Reply


Community Stats

30,162
Posts
114,483
Replies
37,809
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.