I've developed a number of databases in FileMaker for a client, and am now in the process of converting them over to SQL Server. During the transition, there will be duplicates of tables in both platforms, so I must design a method of data synchronization, so that data maintenance need only be done in one database, not both. I've done it with DTS, but that's certainly not the most efficient. Instead, I'm trying to implement Linked Servers within SQL Server so that data can be updated dynamically with SQL statements. Has anyone ever had the pleasure of trying to do this? If so, would you mind sharing with me how you completed the Linked Server dialogue box (see attached) to connect to FileMaker.
I have found the answer. In case you ever have to connect to FileMaker via a Linked Server, here's what you need:
1. The FileMaker file(s) must be opened, and set to Multi-User status and configured to have the Remote Data Access Companion enabled.
2. Set up a System DSN for the FileMaker Pro ODBC driver.
3. In the General tab of the ODBC FileMaker Pro Driver setup, ensure that the "Use Remote Connection" checkbox is checked, and the Server Address field is completed correctly (with either the IP address or the host name).
4. The Linked Server Properties dialog box needs only two entries: Provider Name to be "Microsoft OLE DB Provider for ODBC Drivers" (from the drop-down list), and Data Source to be your FileMaker System DSN as per item 2 above.
It was item 3 above that was causing all my problems. I was running FileMaker on the same (local) machine, and didn't think it would be considered a remote connection.
Sorry if I'm picking up an old thread but I have to move data from filemaker database to sql server. I plan on doing this in linked server is there anywhere that detail these steps that you posted in your thread?