1 2 Previous Next 19 Replies Latest reply on Jan 13, 2016 11:10 AM by deninger

    ODBC Import fails on SOME computers

    deninger

      I have several Macs running a FM Server hosted solution. This solution leverages data from a MS SQL Server by both ESS and ODBC Imports.

       

      The Macs all have licenses (10 connection) for the ActualTech ODBC driver for MS SQL Server.

       

      ESS works still on all of the macs within FM Pro. The ODBC Imports originally worked on all Macs, but now the imports have stopped working on two of these macs (no other changes were made to them).

       

      If I go to the import table and manually execute valid SQL, the some Macs get records but other Macs get no records. Below I show a non-working mac doing a manual import. As you can see, the tables etc show within the Import screens (so the ODBC data source is providing information).

      query.png mapping.pngfail.png

       

      If I use a tool to execute the same exact SQL outside of FileMaker (like iQueryODBC.app), I get the data returned that I expect. This seems to point to FM Pro being the problem.

      iQuery.png

       

      I am most confused and have not been able to identify a problem anywhere. Any insights would be much appreciated!

        • 1. Re: ODBC Import fails on SOME computers
          taylorsharpe

          If you'll set up ODBC on the server and use the Actual Technologies ODBC driver for FileMaker Server (not the clients), I bet your problems will go away.  Using ODBC on client machines means making sure each client ODBC driver is current and working, etc.  If you do it on the server and get it right, it will work right for everyone all the time.  The downside is that the Actual Technologies ODBC driver for server is $400. 

          • 2. Re: ODBC Import fails on SOME computers
            deninger

            I guess I should have mentioned that my server also has an ActualTechnologies license (250 connection) besides the licenses on my clients.

             

            My understanding is that the script step

             

            IMPORT RECORDS [NO DIALOG ; DSN: PHARMASERV ; CALCULATED SQL TEXT: $SQL ; ADD ; MAC ROMAN]

             

            Does the import locally and not on the server.

             

            Yes, keeping ODBC drivers up to date can be a chore, but I don't see that I have a choice. And I have checked, the suspect macs have up to date drivers...

            • 3. Re: ODBC Import fails on SOME computers
              taylorsharpe

              You can run an import locally on a client connected to the FileMaker Server where the FileMaker Server machine is making the ODBC connection.  This is one of the real big benefits of FileMaker server is that it can be the ODBC connection for all of the clients.  Is there a reason you would not want the server to make the ODBC connection instead of the local client?  It usually solves problems like this.  You may have a particular reason, but just checking before delving into what is specifically wrong with those 2 Macs. 

              • 4. Re: ODBC Import fails on SOME computers
                deninger

                Taylor,

                 

                I would PREFER that the server make the connection and do the import (heck I paid BIG $$ for server and the ActualTech server level driver). I guess maybe I am missing something basic here because the import script step appears to occur locally, not on the server.

                 

                Could you describe the general setup where an IMPORT RECORDS script step request made on a client would be done at the server (outside of using the new FM 13's execute script on server function)? I would love to simplify my setup if possible,

                 

                Thanks,

                 

                Mike

                • 5. Re: ODBC Import fails on SOME computers
                  greglane

                  Hi Mike,

                   

                  What you described is definitely puzzling. If you can get help from the admin for SQL Server (or if that's you) you may want to try enabling additional query logging on the client and/or server so you can get details (from SQL Server's perspective) about the query that is failing to return the results you're expecting.

                   

                  Perform Script on Server would be a great option for importing from an ODBC data source using the server's DSN. However, as you said, that requires FileMaker 13 for both client and server, which may not be an immediate option for you.

                   

                  The only other good option for using the server's DSN would be to use ESS instead of Import from an ODBC data source. Go to a layout based on the ESS table and perform a find based on the criteria in your WHERE clause. Then go to a layout based on your target table and do the import. This is more complicated in your example because your SQL query is joining three tables. You can still make this work, but it will require an approach like one of these:

                   

                  • Create unstored calc fields in one of your ESS shadow tables to pipe in the related values.
                  • Do an import (matching) from each of the ESS tables.
                  • Create a view in SQL server that makes your three joined tables appear as a single, flattened table. Then set up an ESS table occurence for the view and do your find on an associated layout.

                   

                  The ESS approach is not nearly as efficient as importing using your SQL query so the performance is going to suffer. It can work though.

                   

                  Hope that helps.

                   

                  Greg

                  • 6. Re: ODBC Import fails on SOME computers
                    taylorsharpe

                    ESS is fine if you want that, as Greg explains, but you don't need it.  When you setup a DSN on the server, FileMaker makes that ODBC connection available to all of the clients.  You do not need to set up any ODBC connections on the clients.  It is all handled on the server.  And when you go to import in FileMaker on a client, it will see the DSN's set up as ODBC connections you have set up in your Manage External Data Sources.  Just make sure that you have added that ODBC External Data Source and you'll be good to go for either direct imports, exports, or ESS. 

                     

                    ESS works great with MySQL and can often avoid having to do ODBC imports and exports if you use ESS.  But ESS can be slow, particularly if if is not a close connection.  However, I have often done FileMaker ESS connections to ESS in the same server rack and just one hop away on the switch and it is really fast.  Then again, I've done ESS over the WAN to Europe from the US and it really was slow. 

                    • 7. Re: ODBC Import fails on SOME computers
                      deninger

                      Okay, so I removed all traces of ODBC DNS on a client machine as an experiment. When I run the script that contains the Import Records script step, I now get an error. If FM Server is supposed to make that ODBC connection available to all of the clients, it doesn't seem to be working.  I have specified the DNS inside Manage External Data Sources (see below).

                       

                      External Data Sources.png

                       

                      In my case, ESS is far too slow and using an ODBC import to grab specific data to be manipulated is an order of magnitude faster.

                       

                      I cannot help think that I am missing something very basic...

                      • 8. Re: ODBC Import fails on SOME computers
                        taylorsharpe

                        Did you remember to do Import via ODBC source instead of Import File?

                        • 9. Re: ODBC Import fails on SOME computers
                          deninger

                          Yes, the import is set to ODBC Data Source...

                           

                          script step.png

                          • 10. Re: ODBC Import fails on SOME computers
                            greglane

                            Hi Taylor,

                             

                            As far as I know, what you've described is only true for ESS (an external data source of type ODBC). FileMaker Pro (and Go) can not directly use the server's DSNs for the Execute SQL or Import Records (from ODBC data source) script steps.

                             

                            I'm not sure you were implying otherwise, I just wanted to make it clear that Mike's choices are to either use the (FMS) server's DSN via ESS or define a DSN on each client (or use Perform Script on Server with FileMaker 13).

                             

                            Greg

                            • 11. Re: ODBC Import fails on SOME computers
                              taylorsharpe

                              Greg... apparently you are correct.  I was seeing the ODBC source because I was using a development machine that had server and client on it and assumed I was seeing the ODBC source fromt the client through the server. 

                               

                              My apologies, deninger.  Greg is correct.  You will need to use ESS for it to work the way I was suggesting.... or go back to what you were doing which is managing ODBC connections on each client. 

                               

                              Which gets back to your orginal problem of those 2 Macs not seeing the ODBC source.  Let me confirm... you verified they have the latest Actual Drivers.  And you said other programs can use the same driver to see data, just not FileMaker.  What is the version of MySQL?  What versions of FileMaker Pro are you using?

                              • 12. Re: ODBC Import fails on SOME computers
                                taylorsharpe

                                Alternatively, could you try a "Perform on Server" where the server machine is configured properly?

                                • 13. Re: ODBC Import fails on SOME computers
                                  deninger

                                  Latest Drivers: Check

                                  Other programs work when FM ODBC does not: Check

                                  The data source is MS SQL Server 2008 R2 (and while I have the "keys" I am not technically the admin as it is for a turnkey solution)

                                  FileMaker Pro 13.x on all machines, including FM 13 Server running on a Mac Mini

                                   

                                  Going the route of Perform on Server" does work, but I have avoided this for a variety of reasons, one of which is an error (I cannot find my notes on it right now) that pops up when many different clients call the same script on the server. I have not had time to figure that issue out up to now either.

                                   

                                  For now, I am calling the script on the server if the client returns no records by itself. I am still stumped by the issue, though...

                                   

                                  Thank you everyone for your help.

                                  • 14. Re: ODBC Import fails on SOME computers
                                    taylorsharpe

                                    Did you try to use the same driver to access the data.  Say use Microsoft Excel to use the same Actual Driver on those machines to see the data. 

                                     

                                    Also, you might compare the update versions of the client programs.  There were quite a number of "issues" with the first versions of 13.... I'd make sure they were up to 13.0v4. 

                                     

                                    Your solution of trying locally and if failing, then on server, is a reasonable compromise.  The server error sounds interesting and probably the next thing to work on which you already know. 

                                     

                                    Wish I could have been more help. 

                                    1 2 Previous Next