Issue about SQLite joint with SQL Server/other database system non-spatial table

[reno.sun.gis]

Hello guys, 

I am a user who is using AIMS 2014 and SQL Server 2008 R2. Recently, I am trying to convert all SDF files to SQLite files for performance improvement. 

However, for the SQLite files, it can work very well without joining with SQL Server non-spatial table but when we joint it with non-spatial table the error showed up: 


The error in the developer tool will be following: 



I think the issue may be about JOIN with SQL Server, but I do not know how to fix it yet. 



However, the strange thing is that when I used SDF that be used to exported the SQLite to join with SQL Server table before, and everything worked well. 

Did anyone here try to join their SQLite file with their SQL Server non-spatial table as well? 

By the way, if I use SQL Server Spatial table to join SQL Server non-spatial table in Autodesk Infrastructure Studio. The same FDO error also poped up... 

Does anyone have any idea/light/solutions for this issue? 

Thank you very much!!!!

[Daniel.Du]

It seems not to be an API issue, let's forward it to product board for more response.

[gluckett]

I have to say I avoid JOINs in Autodesk Infrastructure Map Server since it is always hit or miss (mostly miss) with getting them to work.  

For example: I have a SQL Sever database with two simple tables one with INT/GEOM and the other with INT/NVARCHAR - 2 columns in each table and want to join on the two INT columns.  

Do the two simple INT columns join? no.

So, I end up creating views in SQL Server and then struggle with the Schema Overrides (which expire when I add new tables etc to the database because I have to re-create the Schema Overrides each time the structure changes)

Or finally, since Joins don't work out the box, and VIEWS are a stuggle, I end up completely duplicating my data using Safe's FME Desktop to join my two data sources together.

All because the JOINs don't work very well, if at all, in Autodesk Infrastructure Map Server (versions 2006,2007,2008,2009,2010,2011,2012,2013 and 2014).