7 Replies Latest reply on Mar 25, 2015 4:50 AM by twelvetens

    How to configure ODBC driver on server

    twelvetens

      Apologies for posting here, I know I've read an article from FMI on this, but I can't for the life of me find it.

       

      I've got a client running FMS13 on a Windows Server. They have a FMP solution that they want to be able to communicate with a MS SQL2008 database. I've configured up a FMP13 client machine with the Actual Technologies ODBC driver and everything works fine, but how do I get this to work on the server? Surely I can just configure the ODBC connection on the server, and then I don't have to configure the ODBC driver on every client machine, right? If I want to have scheduled server-side scripts (which I do) to run which will work on the data in the SQL database, surely everything has to be configured server side anyway?

       

      Do I even need an ODBC driver on the server, if it's running Windows Server 2008r2 or whatever? Doesn't that have built in MS SQL ODB Connectivity?

       

      Cheers in advance for any help on this!

        • 1. Re: How to configure ODBC driver on server
          nicolai

          You do need a driver, but Microsoft provides a free one - SQL Server Native Client. Configure ODBC DSN same way as on the client and it should be available in Manage/External Data Sources on the client. Once setup it will be available for all.

           

          There is a chapter in FTS Advanced on adding ODBC to the server.

          • 2. Re: How to configure ODBC driver on server
            justinc

            Hey James,

                 It sounds like you right there, just need a few more details to get it done.  The ODBC guide that you are looking for can be found here.

             

            But, here are some quick steps you can take:

             

            *  You need to start by creating a DSN on the server.  You will configure this DSN to connect to the ODBC source you have, and then the server will be able to use that for scheduled scripts access.

            1) Start by opening up the Windows 'Data Sources (ODBC)' application - you probably want the 64-bit version, but it depends on the source that you are connecting to.  However, the one that is linked to in the 'Control Panel -> Administrative Tools' is probably the 32-bit version (you can double check by looking at the properties of the shortcut in the Admin Tools panel).  You can launch the specific version by going to the specific path:

            • The 32-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\SysWoW64 folder.
            • The 64-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\System32 folder.

             

            2) Create a new DSN (under System is probably best)

            3) Yes, Windows Server should already have the ODBC drivers installed

            4) Configure the server connection as suits your needs

             

            * With that done, you need to connect your DB file to this new source

            1)  In 'File->Manage->External Datasources' click 'New'

            2) Give it a name

            3) Specify that this is an ODBC source

            4) Click the 'Specify' button to see your available DSNs.  The one you created above should show up.

            5) Click save

             

            Now this ODBC source will show up in your lists of Tables in the Manage Database menu.  But you need to create a TO on your graph to be able to specify this new source in your calculations.

             

            Is that it?  I probably left a few details out. 

             

            --  Justin

            • 3. Re: How to configure ODBC driver on server
              nicolai

              Thanks justinc, that's very detailed.

               

              I found the link as well, but the document has one paragraph mentioning ESS. It is mostly on using FileMaker as ODBC source

              • 4. Re: How to configure ODBC driver on server
                nicolai

                all white papers could be found here: Product Documentation

                I do not see anything dedicated to  ESS though.

                • 5. Re: How to configure ODBC driver on server
                  PowerSlave

                  Almost everything, in the case where you don't have FMP/FMPA installed on the server, you will need to exclusively use the 64bit DSN and MAKE SURE THE NAME OF THE ODBC CONNECTION IS THE SAME SPELLING as the one you created when testing on the FMP client. If you DO have FMP/FMPA installed on the server, it's best to create both a 32bit and a 64bit DSN, with the same spelling of the DSN name. When FMS uses ESS, it will use the 64bit driver exclusively.

                  • 6. Re: How to configure ODBC driver on server
                    rrrichie

                    When I developed a solution with this. I installed the Actual ODBC drivers on my development machine, created and tested the solution. ( this was done only to make development easier and not necessary for the final solution )

                     

                    Then I installed the ODBC driver on the Server with the same names uploaded the files to server and it all worked.

                     

                    The solution has ODBC connections to mySQL and Postgress Databases.    

                     

                    In filemaker de ODBC are connected via ESS and act almost like normal tables.  Had a few issues with lots of calls in a row and caching of data.  I save data to shadow records first and then let server loop through these and update the external data (all remote connections).  Retrieving data is done directly.

                     

                    The clients don't need an odbc driver.

                    • 7. Re: How to configure ODBC driver on server
                      twelvetens

                      Thanks All, This has confirmed what I had hoped. Best of all, the client is going to configure the DSN on the Filemaker Server himself now anyway, woop!

                       

                      Cheers