12 Replies Latest reply on Jun 16, 2015 2:17 AM by twelvetens

    ESS create record error

    twelvetens

      Hi,

      Trying to create a record in a MSSQL DB via ESS, I'm getting the exact same error message detailed in this thread:

      http://forums.filemaker.com/posts/7fd8e702a5?page=2

       

      Basically, I get this error:

       

      ODBC Error: [Microsoft][ODBC SQL Server Driver][SQL Server]The target table '<NAME OF TABLE GOES HERE>' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

      [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.

       

      Is this STILL an issue? Surely creating a record via ESS shouldn't be that hard?

        • 1. Re: ESS create record error
          nicolai

          Well, from what I can see, this is an ODBC error and not a FileMaker one. Check you driver version, it could have been fixed by Microsoft.

           

          I suspect, disabling triggers is not an option.

           

          I generally do not insert into MSSQL to keep my dba and dev manager happy. If you really need to, try to use "Execute SQL" instead. If this fails, I suggest creating a Stored Procedure in SQL Server to generate a new record from a parameter and call it with Execute SQL

          • 2. Re: ESS create record error
            taylorsharpe

            Not saying it can't be a bug, but usually when I investigate such situations, it turns out to be a permissions error and you do not have create authority in that table with the account that you are using the ESS.  I would start by trying another tool and using the same User ID/pw to see if it works that way.  If not, go to the DBA for the database and have them check your permissions. 

            • 3. Re: ESS create record error
              beverly

              Yes!

              1. check permissions; along with that are you accessing the tables or views of the tables (as these may not allow INSERT)

               

              2. I always have two SEPARATE dsn set up:

                   a. for use with ESS (where a SQL table alias (occurance) is on the Relationship Graph and accessed manually or by script

                   b. for use with the script steps: IMPORT (for SELECT) & Execute SQL (for INSERT, UPDATE, DELETE)

               

              (this is to verify that all other factors can be ruled out)

               

              3. verify that the correct driver is used.

               

              4. verify that there is a primary key in the table. you should not ever have access to enter into the field on the shadow table.

               

              5. check for other conditions that may prevent creation (a field that cannot be NULL, for example)

               

              6. are there dependancies (other tables) that must be updated (or records inserted) *before* you target table creates the record?

              • 4. Re: ESS create record error
                twelvetens

                Thanks All, I'll get the DBA to check permissions and report back...

                • 5. Re: ESS create record error
                  Orlando

                  Hi James,

                   

                  I have come across this error before and it turned out to be a trigger on the SQL table writing an audit log, and updating data in the same table based on the change. Ask the DBA to switch off any triggers if there are any and test creating records, if this is the case they will have to use different types of triggers.

                   

                  -- Orlando

                  • 6. Re: ESS create record error
                    user19752

                    OUTPUT Clause (Transact-SQL)

                    "If the OUTPUT clause is specified without also specifying the INTO keyword, the target of the DML operation cannot have any enabled trigger defined on it for the given DML action. For example, if the OUTPUT clause is defined in an UPDATE statement, the target table cannot have any enabled UPDATE triggers."

                     

                    This seems ESS makes incorrect SQL it not work with triggers. Why don't use INTO with it?

                    • 7. Re: ESS create record error
                      beverly

                      ESS does not use SQL calls (directly). if you need to change the "call", then use the Execute SQL script step to INSERT, UPDATE, or DELETE. See the xDBC and SQL guides:

                      FileMaker 14 ODBC and JDBC Guide

                      FileMaker 14 SQL Reference

                       

                      beverly

                      • 8. Re: ESS create record error
                        user19752

                        There may be 3 parts that use the errorness SQL OUTPUT statement,

                        1) trigger defined in SQL Server

                        2) ODBC driver for SQL Server

                        3) ESS in FM

                         

                        Only 1st one is able to control by user, so suggestion from Orlando that off the trigger may be only workaround.

                         

                        If using ODBC driver call INSERT statement to create record into the table cause no error, 3rd one is the bad. Then FMI should fix the bug in ESS code

                         

                        Fixing 2 and 3 is software provider's job.

                        • 9. Re: ESS create record error
                          beverly

                          remember that ODBC drivers (for accessing MS SQL from FileMaker) are not FMI products/software.

                          beverly

                          • 10. Re: ESS create record error
                            user19752

                            Yes!

                            If using ODBC driver ... cause error (there is nothing depend on FM), 2nd one is the bad, then MS should fix it.

                            Or, if so, MS may say it is in the specifications

                            • 11. Re: ESS create record error
                              Menno

                              Which version of FMS and which version of MSSQL-server are you using? I have a customer that used a not supported version of MySQL (too old) and he was able do select's but he couldn't write data to text-fields, so update's and insert's went wrong. Maybe you have the same problem with your MSSQL-DB

                              • 12. Re: ESS create record error
                                twelvetens

                                Only just got time to get back to this thread and say a big thanks to all who contributed - all sorted now, using the ExecuteSQL script step and INSERT...