SQL Server Linked Server Connection to FileMaker Pro Advanced 11.3
I have had a linked server solution to a FM Pro database working against FMPro 6.0.
Recently we upgraded to v11.3 and I have been unable to get the linked server connection from SQL Server to work. I have attempted this on both SQL Server 2005 and 2008 R2. (32 and 64 bit machines, using the 32 bit ODBC driver), with the same result.
I have the ODBC dsn configure and it tests successfully. I have created the linked server and it seems to be able to connect, as if I type in the incorrect table name I get FMPro errors saying the table does not exist, as follows
OLE DB provider "MSDASQL" for linked server "FMPRO" returned message "[FileMaker][FileMaker] FQL0002/(1:14): The table named "Sydx" does not exist.".
The table name is "Syd" and when I issue the following SQL:
select * from OpenQuery(FMPRO, 'select * from Syd')
I get the following error:
Cannot process the object "select * from Syd". The OLE DB provider "MSDASQL" for linked server "FMPRO" indicates that either the object has no columns or the current user does not have permissions on that object.
The user has the full access privelege.
I believe there is a bug in the ODBC driver that does not provide the correct listing of the column names?
I continued down a different path and used SSIS with a ADO .Net Provider for ODBC and I could create a datasource that connected. I could preview the data and see my information, but it would not list out the columns and therefore I had nothing to pass to the OLE Destination.. (hence my assumption re: error with column Names).
In the end I used Excel 2010 and connected to the ODBC data source, imported the data into Excel and then did an SSIS import into SQL Server.
This shows that I have the ODBC driver (v18.104.22.168) configured as I could extract the data but nothing I did via SSIS or the SQL linked server connection would work.