• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 960
  • Last Modified:

FileMaker Pro 5 - SQL Linked Server

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
holemania
Asked:
holemania
  • 5
  • 2
1 Solution
 
Will LovingPresidentCommented:
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
 
holemaniaAuthor Commented:
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
 
Will LovingPresidentCommented:
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
holemaniaAuthor Commented:
Thanks will look into this and update.
0
 
holemaniaAuthor Commented:
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
 
holemaniaAuthor Commented:
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
 
holemaniaAuthor Commented:
Thank you.  I am able to get in now.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now