AnsweredAssumed Answered

FM11 ODBC Driver Problems - Doesn't work with SQL Linked Servers

Question asked by ff_mac on Apr 12, 2010
Latest reply on Feb 23, 2011 by ff_mac


FM11 ODBC Driver Problems - Doesn't work with SQL Linked Servers

Description of the issue

Filemaker ODBC drivers have never worked with SQL Linked ServersFM9 and FM10 threw errors when trying to query text fieldsFM11 does not throw an error but returns empty data for all text fieldsConfigurationFilemaker is a 32-bit app and can't talk to 64-bit SQL serverIf you want to connect from the same machine then you can use pro otherwise you'll need advanced serverso to keep it simple I installed both FM11Pro and SQLServer 2008 on a WinXPx86 box.I'm pretty sure I'd get the same results on a Win7 Steps to reproduce in FM11 install odbc drivers from xdbc launch FM11 and open sample.fp7 with login that has full access, enable odbc sharing configure DSN launch SSMS and open linked servers linked server shows up but try to open tables in object explorer error: cannot obtain the schema rowset "DBSCHEMA_Tables" for OLE DB provider, error 7311note that in FM9 and FM10 one could successfully browse table names in object explorerso in this respect, FM11 is actually a step backwardseven though you cannot browse table names in object explorer, you can still query tables select * from fm11sample...membersselect * from fm11sample...companiesin both these cases all rows are returned but no text is shown and no error is returned  In addition, Table names are not mapping properly.  Only tables in the Relationships graph can be queried.  This seems like a bug to me, but I guess you can work around it by making sure all Tables are represented in the Relationship graph with the same name.Note that in all these versions of FM you can successfully query the data in Excel.  But if you want to try and combine FM data with enterprise data in SQL Server, forget it unless you want to write your own webservice to sync local sql tables once a day.I suspect that the Text field problem is in the driver, since it shows up as a conversion error in FM9.  The good thing about this is that a fix would not require a build of FM, just a new version of the odbc driver.  The bad thing is that a driver fix will require DataDirect to get involved.