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):
select * from openQuery(FMPRO, 'Select * from fmdatabase')
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
select * from openQuery(FMPRO, 'Select * from `fm database`')
Now that should all be clear.
Hope it helped.