?
Solved

FileMaker Pro 5 - SQL Linked Server

Posted on 2012-08-16
8
Medium Priority
?
929 Views
Last Modified: 2012-09-04
Hello Experts,

I have a client that is still using FileMaker Pro 5.  They are switching to a new software that uses SQL Server as their database platform.  They have a need to be able to reference the FileMaker data in their report.  Only way I can think of getting the SQL Server Database data and the FileMaker data is through an ODBC that would link the two together.

Question is, has anyone successfully create a linked server from SQL to FileMaker Pro?  If so, can you please provide detail steps?  

http://www.dbforums.com/microsoft-sql-server/984221-linked-server-filemaker.html

I found the above post, but it's an old thread.  Need to understand if I setup the ODBC from the SQL server I am connecting from or what?  Also the ODBC driver for FileMaker, Do I need to install FileMaker on the SQL Server to get that driver or what driver does it use?
0
Comment
Question by:holemania
  • 5
  • 2
8 Comments
 
LVL 25

Expert Comment

by:Will Loving
ID: 38301757
I would strongly encourage them to skip attempting to connect FMPro 5 to the SQL database and instead migrate their FileMaker database to the current version which has SQL Server integration built-in. Any version from 7 on has the capability but it's most robust in the latest version. While it's probably possible to find online somewhere the correct drivers that support ODBC connection in FMP 5, they will likely waste a lot of time trying to get it to work correctly and there may be driver/OS incompatibilities with more recent operating systems.

Converting a version 6 and earlier db to 7 and later can be simple and straightforward if it's a simple system, but if it's a complex system with many tables (in FMP 5 each table is a separate file), layouts and scripts it may require more planning. In general, most conversions simply work, but because of differences between pre-7 and 7-later data structure options, some revision may be required for optimal functionality.

I've written an article on the conversion process which might be useful at:

http://www.experts-exchange.com/Apple/Software/Filemaker_Pro/A_4722-FileMaker-Consolidation-merging-multiple-files.html
0
 

Author Comment

by:holemania
ID: 38302140
Thanks willmcn.  The issue is that they are moving away from FileMaker into another ERP system.  So there's no reason for the upgrade.  However, they have some data from FileMaker that we would like for cross reference with on some of their reports.  We were thinking about extracting these data into Excel and read off there.  Issues, is that it's easier just to reference to these existing table without having to do a lot changes on their report to look at the excel files.

The link I attached in my original thread seems to point that it's possible.  Just not sure on the detail since the steps provided are a bit vague.  Like how do I get the ODBC driver for FileMaker Pro without having to install it on the SQL server so I can connect, etc.
0
 
LVL 25

Expert Comment

by:Will Loving
ID: 38302176
well, you can always download a 30-day trial of the current version of FileMaker. Maybe that would be enough time to do what you want to do.

The steps given in the link refer to the more recent versions of FileMaker I think. As I say, with recent versions of Filemaker, support for MySQL, SQL Server and Oracle is built-in and once you setup the OCBC DSN (Domain Source Name) for the SQL source you can add SQL tables directly to FileMaker's relationship graph. FileMaker then adds a "Shadow table" reference for the SQL table and you can do anything you wish to with it.

Ok, so it just occurred to me that you might want to be using FileMaker itself as the ODBC source and connecting the other direction. If that's the case, I would start with this article: http://www.filemaker.com/support/technologies/odbc.html

You will need to use a current/recent version but perhaps the 30-day trial will be sufficient. If it's not, then I would say they need to weigh the consulting hours required to try to get FMP 5 to do a reliable ODBC connection vs paying for an upgrade. FileMaker Pro 5 was released in September 1999, so you may run into issues with a 13 year old product...
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 9

Accepted Solution

by:
keyu earned 2000 total points
ID: 38304258
0
 

Author Comment

by:holemania
ID: 38331302
Thanks will look into this and update.
0
 

Author Comment

by:holemania
ID: 38347015
The first link seemed like it will work, but still no luck.

I created the linked server, made the necessary changes in FileMaker to allow muliple/local and remote connections.

When I go and do my select statement, it said there's issue with the linked server.

select * from openQuery(FM, 'Select * from C:\FileMaker\Invoice.FP5')

I even tried the following without luck.

select * from openQuery(FM, 'Select * From Invoice)

The database files are in the C:\FileMaker folder with extension .FP5.  Any other ideas?
0
 

Author Comment

by:holemania
ID: 38347964
Okay, didn't realize that I need to set the remote/local access from the FileMaker Pro server application.  I did that and was able to get further.

So I did the following:

select * from openquery(FileMaker, 'Select * from Customer')

It is now querying....somewhat.  My executing query just seems to be running and has been running for an hour now.  It's not pulling anything, so could it be issue with the query?  It didn't give me any error as before.
0
 

Author Closing Comment

by:holemania
ID: 38365190
Thank you.  I am able to get in now.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Integration Management Part 2
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question