4 Replies Latest reply on Feb 17, 2012 2:42 PM by philmee95

    Filemaker Server ODBC IMPORT TO SQL 2005

    the_9th

      Title

      Filemaker Server ODBC IMPORT TO SQL 2005

      Your post

      Filemaker Server and Client 11. Server 2003, MS SQL 2005
      I have been developing solutions for 15 years and would consdier myself an "expert" but this one has me baffled.

       

      Looking to see if anyone else has had trouble getting FM Server to import scheduled data via an ODBC Connection.

      For this example I am using Windows 2003 Server and FileMaker Server 11.

       

      This works completely fine in FileMaker Client, but not on Server.

       

      The file will EXPORT as part of the scheduled script - and then it will say complete. NO ERROR - however the data is not moved from the CSV file to the SQL SERVER (2005) - it will however, work if I send the csv file to FileMaker.

       

      • I have confirmed that the ODBC is operational and named identically on the server and the client
      • I have confirmed that the file is correctly exporting
      • I have confirmed that the path is correctly being set via the variable

       

      I have followed all of the instructions from the FileMaker article on the forum 

      Import/Export Script on FileMaker Server

      The following statement from the article leads me to believe it should be possible.

       

      Supporting import/export formats: . . . In addition, importing/exporting to a ODBC data source is supported.

       

      Any suggestions or advice would be greatly welcome.

       

      Thank you in advance.

      Joe

        • 1. Re: Filemaker Server ODBC IMPORT TO SQL 2005
          the_9th

          This is Joe replying to my own post. I have since switched the file format to XLS and it seems to work just fine. I am not sure why the change has occured, but I am going to accept it for now. If I have time, I will go back and troubleshoot some more. I will post whatever I discover back to the forum here.  :smileyvery-happy:

          • 2. Re: Filemaker Server ODBC IMPORT TO SQL 2005
            philmee95

            yay, forum works in FF (not ie8 though).

            Anyways, have you checked the SQL server DSN is using database auth and not AD user? Nice sticking point with a lot of ODBC clients. Reminds me because I ran into it wirth linked tables and FMP 11, as well as php of course.

            Also, make sure the DSN has the correct default DB, not sue if your script has use xxx; go in it already. Then of course there is the DB perms, but you said it worked when you ran it as a client. How did you log into the fmp script (again, db auth or AD auth)?

            • 3. Re: Filemaker Server ODBC IMPORT TO SQL 2005
              philmee95

               This post seems to be ancient, but I may resort to the "Robot" file (server side scheduled script - import) to run imports via odbc as they seem to not really work as a server script. My server script was set to run a script step that imports via odbc every 2 hours. It has been reporting success for over a year. I just found out that the import has not run in 10 days even though success is reported (via email confirmations). I am guessing that somebody was runing the script manually still everyday and so we did not notice that the server script was not actually running.

              I guess a robot file is like we used to do in filemaker 5...a computer runs macros or windows tasks to open and close a filemaker file that has a perform action on open script step that executes a script in another filemaker database.

              Bogus confirmation email:

              FileMaker Server 11.0.2.217 on fmpserver reported the following event:

              2012-02-17 11:20:13.550 -0800    Information        150         fmpserver Schedule "Inventory Import" completed.

              FileMaker Server automatically sent you this email.  Contact the administrator if you no longer want to receive these notifications:

              someguy arealemailaddress@arealdomain.org

              Phone: nope

               

              I did just see that my script step had "unknown" layout in it while I was verifying server compatibility. This could have cause the issue of not working but ther server still reported success.

              • 4. Re: Filemaker Server ODBC IMPORT TO SQL 2005
                philmee95

                 the Unknown Layout seems to be why the records were not updated via odbc server script step even though it reported completion. Re linking the import to a layout with all the fields fixed this issue for us.

                 

                Just guessing that the_9th might have fdropped a field from his layout that exported a flat csv and that is what happened to that script not really completing successfully.