3 Replies Latest reply on Mar 8, 2010 10:00 AM by TSGal

    ODBC Connection From Hosted Filemaker Database to SQL Server

    tualatinchris

      Title

      ODBC Connection From Hosted Filemaker Database to SQL Server

      Post

      Greeting FM Forum users,

       

      I've got a dumb question which I think I figured out and would like your feedback.  Before asking, here's my setup information:

       

       Server Systems:

       1 Filemaker database hosted on Filemaker Server 9 (through a hosting provider)

       1 MS SQL Server database (hosted on a different hosting provider for our membership-driven web site where users regularly update their profile and contact information)

       

       Client Systems:

       2 OS X systems using Filemaker 9 Pro

       1 OS X system using Filemaker Pro 9 Advanced

       1 PC system using Filemaker 9 Pro

       

      Goal: Show related data from SQL Server through a shared Filemaker database hosted on a Filemaker 9 Server.  (Trivial point: Related data would be linked through my filemaker UserID field = my SQL UserID field.  Just getting the table over into my relationship diagram is the issue at this point.)

       

      Here's the dumb question:

      For a hosted Filemaker database on Filemaker Server 9, should an ODBC connection to a SQL server be created on the host server, or on each client system?  In other words, can I simply ask my Filemaker Server hosting provider to set up an ODBC connection to my SQL server on their end, or do I need to purchase 3 Actual ODBC SQL drivers for my 3 Macs and set 1 Windows ODBC connection on my PC, all with matching ODBC names my Filemaker Database can make use of?

       

      I just purchased one copy of Actual ODBC SQL driver for my Mac, thinking I could set up ODBC on my local client and connect to the SQL server.  When I tried this, the local ODBC connections I created on my Mac client did not appear when attempting to add it to my remotely hosted Filemaker file.

       

      As I was writing this post, I just downloaded a copy of my Filemaker database from the hosting provider.  When opening it locally, I have found that I can now add ODBC connections.  This tells me that my local ODBC connections may not be usable on a hosted Filemaker database.

       

      Advise?  Am I on the right track?

       


      Thanks,

       

      -Chris

        • 1. Re: ODBC Connection From Hosted Filemaker Database to SQL Server
          TSGal

          tualatinchris:

           

          Thank you for your post.

           

          Definitely not a dumb question!  If a database file is set up on the server to access a SQL Server, then clients will be able to view the data without needing a ODBC driver.  So, yes, you can ask your FileMaker Server hosting provider to set up the connection to SQL Server, and the guests will not need the driver to view the SQL Server data.  Make sure the file has all the correct settings to access SQL Server.

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: ODBC Connection From Hosted Filemaker Database to SQL Server
            Ian
              

            Hello TSGal,

             

            I to am trying the same as tualatinchris, I've got as far as installing the ODBC driver on the same machine as the server, I can create a FMP DB (running on the server) from the SQL DB, erase the data and re-import the data from a script, but only locally on that machine.

             

            If I try to run the same script on the same served DB from another guest machine I get an error it can't find the ODBC driver.

             

            How can I get the server to "see" the file so any other guest can work with it, without installing the driver on the guest machines?

             

             

            Update: I found that I can add the ODBC DB in the Relationships Graph, only now I get:

             

            ODBC communication / Link error. Please check your network connection and / or database server.

             

            My import script still works fine, any ideas? 

             

            Thanks

            Ian

             

            FMP Server 9 Advanced 

            • 3. Re: ODBC Connection From Hosted Filemaker Database to SQL Server
              TSGal

              lans:

               

              Thank you for your post.

               

              It sounds like you are trying to access the SQL database directly from the client.  If the information is not already downloaded to the host, then the client/guest will need a driver to access the data.  Therefore, make sure the data is downloaded to the FileMaker Pro database.  That way, the guests will not need the driver because the information is at the host machine.

               

              TSGal

              FileMaker, Inc.