11 Replies Latest reply on Dec 14, 2016 11:22 AM by user27087

    ODBC Import, SQL Server 2016

    user27087

      I understand that SQL Server 2016 is not officially compatible, and I did read that someone managed to "get it working" but there were no details.

       

      Does anyone have positive experiences with ODBC imports from SQL Server 2016? This will be for FileMaker Pro 15 users, Windows. Specialty import needed by one user of a large company, so FMP client or FMS can perform import.

       

      Thanks in advance.

       

      Scott

        • 1. Re: ODBC Import, SQL Server 2016
          taylorsharpe

          FileMaker 15 came out last May and Microsoft SQL Server 2016 came out in June, so there was no officially released version of FileMaker when MS SQL Server came out.  But ODBC imports are usually extremely compatible and I'm sure work just fine with FileMaker.  However, the more complicated External SQL Sources (ESS) which allows you to put SQL tables in FileMaker's relation graphic requires a lot of work by FileMaker to assure compatibility through shadow tables and other techniques to make it work seamlessly.  But I would not be surprised if the upcoming FM 16 will be certified to have ESS work with Microsoft SQL Server 2016.  If you just want to do regular ODBC imports and nothing with ESS, then you are really safe. 

          • 2. Re: ODBC Import, SQL Server 2016
            fmpdude

            You can write Java code using JDBC to interact with both SQL Server and FileMaker.

             

            FMP includes a free JDBC driver.

            MS includes a free JDBC driver.

             

            Add code and stir.

             

            HOPE THIS HELPS.

            1 of 1 people found this helpful
            • 3. Re: ODBC Import, SQL Server 2016
              beverly

              These threads are addressing this issue:

              (and subsequent Idea request)

               

              There have always been those who try to work outside the box, but keep in mind that the risk is yours if you do so. Promote the idea (vote up) and wait until a driver is available that works with SQL 2016 (for FM).

               

              beverly

              • 4. Re: ODBC Import, SQL Server 2016
                beverly

                nice answer, fmpdude! Yes, of course. You can make FMP the ODBC/JDBC source, if SQL server can push the data to it.

                 

                and on occasion, I've used web interface (PHP or other web apps) that can speak to both (with XML or xDBC) to exchange data.

                 

                another option: before the current version of SQL server did not work (at the time), but the previous one could communicate with FM, I've had SQL speaking to SQL, then passing on to FM. (we do what we must!)

                 

                beverly

                • 5. Re: ODBC Import, SQL Server 2016
                  user27087

                  Thanks all for the replies. I'll post back with results (or lack of) when I get access to the SQL server. I'm working with a company that has taken a year (yes, an entire calendar year) to begin conversation on allowing ODBC read access, and only to be read from a data warehouse.

                   

                  They are so bad at general database concepts that even my average SQL abilities surpasses the programmers they have assigned to me. They are having trouble understanding what a VIEW is, and I can imagine they had to look it up after I suggested it.

                   

                  I don't trust them to "push" data to FMS as an ODBC source, and I can't afford to have another run-around if I ask for another technology. I hope to have results within the next year.

                  • 6. Re: ODBC Import, SQL Server 2016
                    fmpdude

                    Man...this is so easy with Java and JDBC.

                     

                    ODBC blows in so many cases (IMHO), as in ... "it doesn't cut it."

                     

                    Unless you can get an ODBC driver that works inside FMP and has reasonable performance, just use JDBC. So straightforward!

                     

                    HOPE THIS HELPS.

                    • 7. Re: ODBC Import, SQL Server 2016
                      taylorsharpe

                      Ahhh... the inflexible corporate IT staff are sure to hold you back all the while telling their managers they know more than you when they usually don't.  Good luck with it user27087!

                      • 8. Re: ODBC Import, SQL Server 2016
                        fmpdude

                        +1


                        taylorsharpe is so right.

                         

                        Man, using JDBC, I would have been DONE by now.

                         

                        (Next project, please...)

                         

                         

                        • 9. Re: ODBC Import, SQL Server 2016
                          user27087

                          ODBC import works wonderfully. After waiting 10 months for this company's IT to give me access, it took 5 minutes for me to sign in, Import ODBC, create new table, and write an import script.

                           

                          Importing 2 million rows took only a couple of minutes. I'll get back with driver details in case anyone else in in need of ODBC import from SQL Server 2016, because the driver version very well could matter.

                          • 10. Re: ODBC Import, SQL Server 2016
                            beverly

                            yes, please post the driver information!

                            beverly

                            • 11. Re: ODBC Import, SQL Server 2016
                              user27087

                              Driver: SQL Server v6.01.7601.17514

                               

                              I have no choice because it's what was on my temporary user PC, but it works. I do not have access to System DSN so the data source is working with User DSN.

                               

                              About to request that the FM Server itself perform the connection, so I may be reporting back on the version that works with FMS.