8 Replies Latest reply on Feb 9, 2015 9:32 AM by Jason_Farnsworth

    Using FileMaker as a linked server in SQL Server 2008 R2

    Jason_Farnsworth

      In order to use FileMaker data within an ArcGIS web service, I am attempting to use SQL to copy the data from a FileMaker table into a SQL Server table. I have created the connection to the FileMaker Server via a linked server in SQL Server Management Studio. While experimenting with using Transact-SQL OPENQUERY to retrieve data from the FileMaker table I encountered some issues:

       

      • When trying to write to the FileMaker table, I receive an error stating that I do not have proper authentication to the FileMaker server, despite the fact that I established the connection and am performing all queries with a full-access FileMaker user profile.
      • When trying to copy data from a FileMaker table, I receive an error stating that the table either has no data in the column specified or that I do not have proper authentication. The table does have data in the column specified and I am using proper authentication.
      • It should be noted that I am able to retrieve all of the fields in an empty table using the * wildcard. However, when trying to use the * wildcard on a full table, I receive the same “column may be empty or improper authentication” error.

       

      Are these issues a bug or is there something I am doing incorrectly?

       

      Thanks in advance,

       

      Jason Farnsworth

      Midland, TX

        • 1. Re: Using FileMaker as a linked server in SQL Server 2008 R2
          thewoz

          I've encountered a variety of problems trying to directly link Filemaker to SQL files for GIS applications. My workaround is to use Filemaker to enter attribute data for GIS point data because it is easier to set up the front-end to control QA during data entry in Filemaker than it is in a SQL table or in a GIS table. Then I set up scripts to export the data to an Excel file (sometimes to a .dbf file, but it has a 10-character field name length that can cause problems). The script replaces the Excel file each time and ensures that the same fields are exported each time. The GIS file is set to automatically join (I'm dealing with one-to-one relationships) the Excel file to the GIS theme. Thus I see the new data each time I open the GIS file. This works reasonably efficiently but ArcGIS does not automatically update joined files in an open session so you may have to close the mxd file and then reopen it to force Arc to reload the new file. This is not a particularly elegant solution but I've found that it is reasonably efficient and gets the job done.

          Karl

          • 2. Re: Using FileMaker as a linked server in SQL Server 2008 R2
            taylorsharpe

            Not having access may be an issue of extended privilege sets to include ODBC connections.  It can even be turned off for [Full Access].  Make sure the account you are using to make the connection and write to FileMaker has this extended privilege checked. 

             

            SQL is not all the same.  FileMaker uses SQL-92, which is a fairly old standard and is an official ISO standard.  If it works in SQL-92, it will work in most other SQL versions.  However, the converse is not true and lots of people try to use advanced features in newer SQL versions.  So for us to help, give us your SQL statement and we may be able to provide some additional assistance. 

            • 3. Re: Using FileMaker as a linked server in SQL Server 2008 R2
              matthew_odell

              Hey Jason, FileMaker ODBC does not support SQL Server's Linked Server functionality, which is probably why you are getting those errors. I'd suggest filing a feature request here: http://www.filemaker.com/company/contact/feature_request.html

              • 4. Re: Using FileMaker as a linked server in SQL Server 2008 R2
                taylorsharpe

                FYI, I put in a feature request to upgrade FileMaker SQL from SQL-92 to at least SQL1999 or maybe even SQL-2008.  It sure would be nice to have additional features like recursive queries, xml features, instead-of, etc. 

                • 5. Re: Using FileMaker as a linked server in SQL Server 2008 R2
                  Jason_Farnsworth

                  Karl,

                                    Thanks for the suggested workaround. I will try to implement something like this for the time being and see how well it works for hosting the data via GIS web services.

                  Jason Farnsworth

                  • 6. Re: Using FileMaker as a linked server in SQL Server 2008 R2
                    Jason_Farnsworth

                    Taylor,

                    I have had to make sure that the account had all privileges for ODBC/JDBC connections for a separate part of this project, sorry that I didn’t mention that as well.

                    Furthermore, I’m executing a fairly simple SQL statement, so I would assume that it is compatible with SQL-92. I am using Transact-SQL to execute an open query, but that would not be handled by the FileMaker server. All the server should get is the SQL statement from within the open query, correct?

                     

                    Here are the queries I’m testing, the first one is working and the second is not.

                    SELECT * FROM openquery(LINKEDSERVERDB, 'SELECT * FROM Test');”

                    This query successfully returns all of the fields in a table. There are no entries in the table to return.

                     

                    “SELECT * FROM OPENQUERY(LINKEDSERVERDB, 'INSERT INTO Test (Field_1Text) VALUES (''Test'')');”

                          This query fails to execute and returns this error:

                    The OLE DB provider "XXXXXXXXX" for linked server "LINKEDSERVERDB" indicates that either the object has no columns or the current user does not have permissions on that object.

                     

                    Jason Farnsworth

                    • 7. Re: Using FileMaker as a linked server in SQL Server 2008 R2
                      Jason_Farnsworth

                      Matthew,

                                      I did not realize this whenever I began working with the two. Thanks for your help, I will file a feature request.

                       

                      Jason Farnsworth

                      • 8. Re: Using FileMaker as a linked server in SQL Server 2008 R2
                        Jason_Farnsworth

                        Taylor,

                        Thanks for that. I will make sure to put in a request as well.

                         

                        Jason Farnsworth