Step1 - Download/Install the Filemaker ODBC driver for your version of Filemaker
http://www.filemaker.com/support/downloads/?csr=support_downloads_new
Step2 - Configure the Filemaker Databases for sharing according to chapter 4 in the documentation
http://www.filemaker.com/support/product/docs/12/fmp/fm12_odbc_jdbc_guide_en.pdf
- Enable Sharing
- Enable Multi-User
- Enable Remote Access
- Add a "maximum number of characters" in the text field validation option settings in the Filemaker database. Set it to 255.
Step3 - Create ODBC connection to Filemaker using Filemaker ODBC driver
- Admin tools>ODBC>System DSN>New> Create a connection, call it "FM"
Step4 - Create Linked server in SQL
- Provider is Microsoft OLE DB Provider for ODBC
- Product name is Filemaker
- Data Source is Your ODBC name "FM"
- Under security, enter the Filemaker Admin username and password
Although you can now connect to the database via linked server, none of the tables can be browsed or scripted normally.
You must use
select * from openquery(FM, 'select * from "table name with spaces"')
to select any data.
Filemaker uses double quotes " " instead of [ ] to enclose table names
Hi Cowmaster, Thanks for the article it was very useful. I am having issues with the select clause when using where and a text. Have you had any luck supplying a condition in your FM SQL query?
ReplyDeletee.g select * from "table name" where field = text does not work and i get the following error.
OLE DB provider "MSDASQL" for linked server "Filemaker" returned message "[FileMaker][FileMaker] FQL0007/(1:35): The column named "Resolved" does not exist in any table in the column reference's scope.".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "select * from "RA" WHERE zkp_ra_id="Resolved"" for execution against OLE DB provider "MSDASQL" for linked server "Filemaker".
However select * from "table name" where field = number works
Thanks
I haven't done too much more testing with this, but i would suggest inserting the entire result into a temp table, and then querying on that to get what you need. select * into mytemptable from (select * from openquery(FM, 'select * from "table name with spaces"'))
ReplyDeleteHave you been able to INSERT records into FM from the context of MS SQL? I have been attempting to get this to work with OPENQUERY but thus far have not had success. Specifically FM doesn't like distributed transactions. However, I can insert records through a JDBC connection just fine.
ReplyDelete