Ok,
This one may be a bit obscure but I had to do this for data conversion from a legacy system the other day and the FileMaker documentation was pretty light on, but I eventually got it to work.
The problem is that you want to extract data from a FileMaker Pro 6.0 database by creating a linked server connection from SQL Server.
Here are the steps with one Caveat. I was only able to make this work on a 32-bit machine. I had a 64-bit machine, and I could run the 32-bit odbc datasource tool and set-up and test the connection but I could not get my 64-bit version of Sql Management studio to find the 32-bit System DSN I had created, so I jumped over to a 32-bit virtual machine with SQL 2008 on it and everything worked more easily.
1. Install FileMaker Pro
Ensure that you have FileMaker pro installed on the box that you want to create the linked server connection on. This installs the 32-bit filemaker pro driver to allow you to create an ODBC data source.
2. Create on ODBC Datasource System DSN
- Go start :: administrative tools :: Data Source (ODBC)
- Switch to the System DSN tab
- Click Add
- Choose “FileMaker Pro”
- Click Finish
- On the General Tab give the connection a generic name, (can be anything), i used FMPro
- Tick “Use Remote Connection” and add the loop back IP address, 127.0.0.1
- Click OK.
3. Create a linked server connection
- Launch SQL Server Management Studio
- Expand to “Server Objects” :: “Linked Servers”
- Right Click “New Linked Server”
- In the provider choose “Microsoft OLE DB Provider for ODBC Drivers”
- In the following fields, enter “FMPRO”
- Linked Server
- Product Name
- Data Source
- Click OK.
4. Load FileMaker Database
The linked server connection only works via the local client, so for the linked server to work you have to have your Filemaker Pro database loaded and remotely accessible.
- Launch FileMaker Pro
- Open your database, this can be a FM database on a remote server over TCP/IP, connect up as you normally would to access the file.
- Choose File :: Sharing
- Set to Multiple and enable Local and remote connections.
Note: During my linked server sessions, I have sometimes lost the connection or get connection errors. I just close filemaker, and repeat the above steps and it all comes good again.
5. How to query FileMaker
What is the name of the FileMaker Table? select * from what?
Turns out the name of the filemaker database is the table name, so assume your db is called “fmdatabase” you would write the following (Note: I generally use “OpenQuery” statements to run my linked server queries):
[sql]select * from openQuery(FMPRO, ‘Select * from fmdatabase’)[/sql]
but what if your filemaker database has a space in the name? like “fm database”
well the trick here is documented away deep in the FileMaker help file use `
this is the punctuation on the tilde (~) key.
so your query becomes
[sql]
select * from openQuery(FMPRO, ‘Select * from `fm database`’)
[/sql]
Now that should all be clear.
Hope it helped.
Thanks for posting this as it was very helpful. However, did you notice horrendous performance on larger FP databases? I found there is way to fix this, but only on the newer versions of FP?
http://help.filemaker.com/app/answers/detail/a_id/6120/kw/slow%20odbc
Thanks a lot, it’s very useful, but my problem is I couldn’t open table and SQL show DBSchema_table error. I use Pro Adv 11.
Hi, I assume you get the DBSchema error when you setup your linked server connection and try to drill down to the tables, (when looking via the SQL Management studio in the “Server Object::Linked Servers” section).
This is fine because the ODBC driver does not provide the ability to list the tables. You need to find the name of the tables in File Maker (“File::Manage::Databases”). I would make sure you use a table name without spaces and then you should be able to issue a query like:
Select * from OpenQuery(FMPRO, 'select * from tableName')
and it should work.
I say should work because I have now run into the same issue. 2 days ago I had to do try to setup a linked server connection to FileMaker 11 and I could not get it to work. I have an open query on the FileMaker pro forums to try to get to the bottom of it. Below is the details of my post on that forum that I am still awaiting some kind of response on.
I will do a post on how to setup the ODBC connection to Filemaker Pro 11 in the future, but here are some tips:
you can only setup a single ODBC connection to a single FMPro Database at a time. Tick the box to says”Connect to host to obtain the names of the available databases”, to load the databases that are available and if you get nothing in the list this means you probably have a problem with the security and or sharing settings on your FileMaker Pro database.
To make it easy setup a login with full access permissions.
Double check the entitlements and make sure that the extended priveleges allow “Full Access” to have “Access via ODBC/JDBC” and “Access via filemaker network” enabled
Make sure you enter the username and password in the Linked server connection, select “Be made with this security Context” on the Security tab and enter those credentials that have full access to the FileMaker Database
The following are the details of my post on the filemaker forum:
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 (v11.3.76.0) configured as I could extract the data but nothing I did via SSIS or the SQL linked server connection would work.
Check here to see if anyone has a solution