FileMaker to SAS via ODBC?
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
Obtain access to data
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."
FM 220.127.116.11, 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.
Manual solution (create a series of exports for each table, save separately, write programs to read & relate, check results, write programs to link tables).