1 2 Previous Next 19 Replies Latest reply on Apr 4, 2017 7:14 AM by beverly

    Filemaker to SQL through ODBC connection

    jeffwest2

      I am totally new to FM, having worked in a SQL.My SSQL environment for years, the company I work for has recently started using FM for a specific purpose as a CRM system

       

      I can see many questions on this and have followed instructions off a couple of them to try and get this working, to no avail, so here is what I have done.

       

      I have an existing FM Pro 15 with a standard set up, I want to set this up so it connects to a SQL database as an ESS not as a full datasource, mainly for reporting externally into SSRS but also for ease of automating some data exports on a daily basis.

       

      Following suggestions in older posts, I have set the odbc/jdbc settings to on, and have (at the moment) set to all users, although this will be restricted later if i get this working).

       

      I have set up 2 odbc connections to my SQL database

      1 in ODBC32 and 1 in odbc64, both are differently named, but connected to the same SQL database

      I am now trying to set up a new external data source in FM, this i do by going to

      File-Manage-External Data Source

      Click New

      Click ODBC

      Click Specify at DSN, and this is the issue, I have nothing here to select, what am I doing wrong?

       

      Any help with this would be really helpful as I need to get this working before we deploy a new FM system live in a couple of weeks.

        • 1. Re: Filemaker to SQL through ODBC connection
          Johan Hedman

          First off start to read free FileMaker Training Series to understand what FileMaker is and how to work with it

           

          FileMaker Training Series | FileMaker

           

          Then when you have finished that you can read more about ODBC and ESS in FileMaker

           

          Accessing External SQL Data Sources (ESS) Overview and Troubleshooting | FileMaker 

          • 2. Re: Filemaker to SQL through ODBC connection
            jeffwest2

            JOHAN

             

            Thanks for the links, some of what you have suggested I have looked at, I can import data into my FM database without a problem from a current SQL data which is where the initial data for the FM project is going to come from.

             

            Looking at the ODBC and JDBC guide I seem to have everything it says up and running, so this is just nuts that it doesn't work, I will go through it again and see if I have missed anything though.

            • 3. Re: Filemaker to SQL through ODBC connection
              Johan Hedman

              I would setup a ODBC between your SQL-database and then use the tables from your SQL-database as ESS-tables inside FileMaker. Then you do not have to import records.

               

              If you want your two solutions to be apart and just sync data you can use the tool MirrorSync that I can recommend

              360Works MirrorSync: FileMaker Sync for FileMaker Server, FileMaker Pro, and FileMaker Go on iPhone and iPad

              • 4. Re: Filemaker to SQL through ODBC connection
                jeffwest2

                Johan

                 

                If you are suggesting leave the data in the old database and connect this to FM via the ODBC I can't, this is a project I am coming into right at the end not the start, all the work has already been done to build FM for it's use, I have now got the challenge of getting the data out of the old database into FM, (almost done), then trying to do daily exports for clients and reporting purposes.

                 

                The easiest way for me is to download the data daily into SQL and use it from there in the new structure, not sure i can use mirror sync but I will have a look at it and see if it meets the need I have.

                • 5. Re: Filemaker to SQL through ODBC connection
                  Johan Hedman

                  The I would setup MirrorSync and then have a test sync before you do the final sync of data

                  • 6. Re: Filemaker to SQL through ODBC connection
                    wimdecorte

                    jeffwest2 wrote:

                     

                    I want to set this up so it connects to a SQL database as an ESS not as a full datasource,

                     

                    Not sure what you mean by this and it may be important for us to understand your thinking around this.  What is a 'full datasource' and how it is different than ESS for you?

                    • 7. Re: Filemaker to SQL through ODBC connection
                      wimdecorte

                      jeffwest2 wrote:

                       

                       

                       

                      I have an existing FM Pro 15 with a standard set up, I want to set this up so it connects to a SQL database

                       

                      Following suggestions in older posts, I have set the odbc/jdbc settings to on, and have (at the moment) set to all users,

                       

                      That last sentence sounds like a setting you have done in FM; that is irrelevant to what you want to achieve in your first sentence.   Toggling on odbc/jdbc in FM is to give access to the FM data from outside FM, not to give FM users access to outside data....

                      • 8. Re: Filemaker to SQL through ODBC connection
                        jeffwest2

                        May well be terminology as far I am concerned, as I think I said I have had no say in the set up for FM, as far as i see it, the standard db for FM is a flat file, this to mean is the FM datasource.

                         

                        I want to use SQL as a form  of external data warehouse so I can more easily perform SQL code on it for exports and SSRS reporting to clients and internal stakeholders, this to me is the ESS element.

                         

                        As I say, this may be about wording, and coming from a SQL background how I would term things.

                        • 9. Re: Filemaker to SQL through ODBC connection
                          jeffwest2

                          wimdecorte

                           

                          wimdecorte wrote:

                           

                          jeffwest2 wrote:

                           

                           

                           

                          I have an existing FM Pro 15 with a standard set up, I want to set this up so it connects to a SQL database

                           

                          Following suggestions in older posts, I have set the odbc/jdbc settings to on, and have (at the moment) set to all users,

                           

                          That last sentence sounds like a setting you have done in FM; that is irrelevant to what you want to achieve in your first sentence. Toggling on odbc/jdbc in FM is to give access to the FM data from outside FM, not to give FM users access to outside data....

                          Correct, I do not want the datasource of my FM project as an external database (i.e. SQL), i want an external SQL database to connect to the FM datasource, so I have done the correct thing by toggling the odbc/jdbc switch to on and set the users as well from what I have read in documents and posts from this site, my issue is, which again, from the posts and documents on this site i have read, suggests I need to do. while i can set up a ODBC connection, I cannot see this ODBC from within FM to set it as an external source.

                           

                          I am sorry if i am not being very clear and that maybe wording and terminology are getting a bit confused on my side which is not helping.

                          • 10. Re: Filemaker to SQL through ODBC connection
                            beverly

                            I'm confused and I'm a SQL dba.

                             

                            1. FileMaker as ODBC/JDBC source means that other apps can interact with FMS (& data).

                             

                            2. FileMaker looking AT ODBC/JDBC source means:

                                 a. ESS (where a SQL 'table' or 'view' is set as an alias - aka "table occurrence" - on the relationship graph in FileMaker. This allows the data to be used in a way as any other data (& tables), natively in FileMaker. NO SQL calls required.

                                 b. to SQL source but not "live connect" like ESS and using these two Script Steps:

                                      1) Import - using SQL SELECT query - all data is owned by FMP at that point and there is no connection to SQL (so changes are not reflected in SQL)

                                      2) Execute SQL - where SQL INSERT, UPDATE, or DELETE queries may be performed.

                             

                            Does that help and can you then explain more of what you wish to do?

                            beverly

                            • 11. Re: Filemaker to SQL through ODBC connection
                              beverly

                              Also these may help us help you:

                               

                              What version (and platform) of FileMaker Pro (32/64)?

                              What version (and platform) of SQL?

                              What ODBC drivers (to SQL) are being used?

                              • 12. Re: Filemaker to SQL through ODBC connection
                                user1859

                                I think the ODBC setup has to be a System DSN not a user DSN. I also noticed the there is a difference between Filemaker and FMServer one uses the 32 DSN and server uses the 64 DSN. Make you DSN setup is system and not the user.

                                • 13. Re: Filemaker to SQL through ODBC connection
                                  LSNOVER

                                  ESS is used to bring data from a SQL DB INTO Filemaker.  Absolutely no use for querying FM Data from a SQL database.  ESS can be used to INSERT/EDIT/UPDATE and Delete data in SQL databases, all FROM WITHIN Filemaker.

                                   

                                  ODBC can be used to make FM accessible to other Query engines, and it can be used to import data into Filemaker. Setting up ODBC for your SQL databases is a necessary step to utilize ESS.

                                   

                                  As WIM pointed out, we need more information on what you are trying to do from where, to be of more assistance.

                                   

                                  Kind regards,

                                  Lee Snover

                                  • 14. Re: Filemaker to SQL through ODBC connection
                                    jeffwest2

                                    Hi Beverly

                                     

                                    I will try and make things a bit clearer.

                                     

                                    I have two things going on her, the first is the import job from an existing 2005 SQL database into Filemaker, once this happens, the old DB will be decommissioned, so this is out of the picture entirely.

                                    I have this set up as a link to the current SQL database, which after clicking import I can use this to run SQL code/Stored procs to import the data I need

                                     

                                    What I want to do is set up an automated export job from Filemaker into a brand new SQL database, for testing a 2008R2, but more likely a 2014, so that I can more easily using SQL code SSRS reports and some other data extracts for clients and interested parties internally..

                                     

                                    Filemaker is currently sitting on a 2008 R2 64 bit server, but again this may change later in the year, (this has not been set up by me, I am coming at this after the fact)

                                     

                                    On the server that FM sits on I also have a 2014 instance of SQL.

                                     

                                    Within ODBC i have set up 2 SQL Server odbc links to a created external 2008R2 database I have set up

                                    1 link is through the 32 bit ODBC

                                    1 link is through the 64 bit ODBC

                                     

                                    Neither of these is showing within FM as an available Data source, how can I set this up if i cannot see the datasources, and how do I get the sources to show?.

                                    1 2 Previous Next