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
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.
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?
Thanks All, I'll get the DBA to check permissions and report back...
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.
"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?
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.
remember that ODBC drivers (for accessing MS SQL from FileMaker) are not FMI products/software.
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
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
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...