AnsweredAssumed Answered

FileMaker to SAS via ODBC?

Question asked by RogerWirt on Sep 14, 2010
Latest reply on Jul 31, 2013 by RogerWirt

Summary

FileMaker to SAS via ODBC?

Product

FileMaker Pro

Version

11 (Advanced)

Operating system version

Win XP SP3

Description of the issue

With a confirmed Windows ODBC DSN and FileMaker internal privileges & permissions set, clients on the same PC can 'see' header information for FileMaker table occurrences but can't import data.  One exception occurred when Excel 2007 successfully imported a flat file of 2 columns and 3 rows;  but for a complex multi-table FileMaker system, no data could be obtained over an identical connection with either Excel, SAS 9.2, or SPSS [PASW] 18.  SAS and SPSS displayed table information about the FileMaker system, but attempts to display data from any given table with SAS yielded message that the table did not exist;  SPSS displayed a blank table with some header information that appeared to correspond to FileMaker variable list. 

In this office, the same methods have routinely worked to link SPSS and SAS with such database systems as Oracle, SQL Server, and Sybase.

Steps to reproduce the problem

Open a database in FileMaker
Create Windows DSN
Open client (e.g. SAS)
Instruct client to open FileMaker database

Expected result

Obtain access to data

Actual result

Client software reports a successful connection, displays FileMaker table-occurrence names, but does not provide access to data.

Exact text of any error message(s) that appear

SPSS: none.  (displays an empty table, with header info clearly obtained from within the FileMaker database)

SAS: "Libref was successfully assigned" (i.e. SAS can "see" the FileMaker database, recognizes a working ODBC connection), and FileMaker table occurrences are recognized (displayed with a series of little icons in SAS Explorer) .. but no data can be extracted.  Sample error messages:
  1. SAS Explorer: "The table ... either does not exist or cannot be displayed."
  2. SAS SQL: "File ... does not exist."

Configuration information

FM 11.0.2.0, FM ODBC driver 11.0.61
SAS 9.2 M0 with associated (SAS 9.2) ODBC driver
SPSS ('PASW') 18.0.0
XP Pro SP3

FileMaker's internal configuration settings were confirmed by an expert FileMaker developer who replicated the above steps with Excel and SPSS.  Essentially similar procedures with SAS on the same PC work fine to connect to a remote SQL Server database.

Workaround

Manual solution (create a series of exports for each table, save separately, write programs to read & relate, check results, write programs to link tables).

Outcomes