6 Replies Latest reply on Jun 12, 2017 9:15 PM by actualjon

    Playing nice with SQLserver

    JohnStewart

      Hello,

       

      was wondering if anyone can offer advice. I am trying to use filemaker as a front-end program to manipulate data already held on a SQL server. I can view and edit this data no problem through an odbc connection. However, the particular program requires a value to be set of CONTEXT_INFO and I cannot find a way of defining this value.

       

      Anyone any suggestions?

       

      Thanks,

      John

        • 1. Re: Playing nice with SQLserver
          taylorsharpe

          There are quite a number of versions of SQL out there.  FileMaker has chosen to support the 92-500 version, which is a rather old one, but it is also very universally supported across platforms.  You have chosen a SQL function that is not supported in FileMaker's version of SQL.  So it just won't work in FileMaker. 

           

          FYI, I have wished FM would update to newer version of SQL standards. 

          1 of 1 people found this helpful
          • 2. Re: Playing nice with SQLserver
            fmpdude

            I don't use ODBC, so please pardon this question if it doesn't apply, but Isn't the version of SQL Server supported more related to what version of the ODBC third-party software you're using?

            • 3. Re: Playing nice with SQLserver
              beverly

              The ODBC driver, yes may play a part.

              However, it may be a matter also of using the ESS (live connect) vs. the script steps:

              • Import

              • Execute SQL

               

              There may be ways to function with the script steps that are not possible with ESS. But even then not all things "FileMaker" can translate into everything SQL (various dbs and version) and the other way, of course.

               

              At this point for OP, it may also be that FMServer needs to be the ODBC/JDBC source and let the SQLserver try to play with it.

               

              Also, I typically add:

              Do not rely on FM as a "frontend" to SQL "backend"

              There are limited things that can be done, yes, and these are wonderful. However, I might work through a 3rd party app (probably web-based) that can communicate with both dbs.

              IF I have control of both FMS and SQL server, then that is a good way to go, too.

               

              See if the script steps add something that helps or FMS as source.

              Beverly

              1 of 1 people found this helpful
              • 4. Re: Playing nice with SQLserver
                LSNOVER

                Are you using ODBC based imports and updating data via scripts, or are you using the ESS feature?  Are you running in a Filemaker Server environment?

                • 5. Re: Playing nice with SQLserver
                  JohnStewart

                  ESS feature using Actual ODBC driver and working on a database hosted filemaker server. I want to make some very basic changes to 1 field in a few tables to save hours of manual data entry in another program. I can do it no problem with a script, but the database has a trigger to update a field (for syncing) based on the CONTEXT_INFO of the session when altered. There doesn't seem to be any CONTEXT_INFO defined. If have tested it by changing the trigger but I don't really want to change the hard_coding of the original database.

                  • 6. Re: Playing nice with SQLserver
                    actualjon

                    Is the CONTEXT_INFO value a constant (i.e. is it something that could be set at connection time)?  The driver has a (hidden) "InitSQL" DSN parameter that would let you execute a SQL statement right after the connection is made.

                     

                    Also, are you free to create a new table or view within the database?  If so, would you be able to put a trigger on that table / view that sets the CONTEXT_INFO?

                     

                    Jonathan