FMSA11 ESS / ODBC intermittent error
Having an intermittent issue here and would appreciate any info anyone might have. First, a brief bit of setup:
We're using FMSA 11 on Mac, and it has an ESS connection to a table in a remote MS SQL Server. Browsing data works (though it is painfully slow - the connection is over a WAN).
The app needs to insert a record. I know the first choice is to use ExecuteSQL to do so, but we need to do it via ESS. Execute SQL requires client ODBC connections, ESS only requires the Server ODBC connection. So, we have a script that switches to the ESS table's layout, creates a new record, sets all the fields, and commits the record. This doesn't work 100% of the time, though. Some records fail. However when the FM Client is restarted, the exact same script with the exact same data succeeds. Sometimes we'll go a full day with no errors, sometimes we'll get dozens of errors inside an hour.
To try some troubleshooting, we attached SQL profiler, and here's what we saw:
During a successful update,
It's a bit of a round-about way of doing an insert, but that's OK. It works.
During a failed update,
Obviously, without opening the cursor, SQL won't let you execute an insert against it. Since we're in an ESS scenario, we have no control over the emitted SQL, so we're at a point here where we're ready to ditch ESS altogether and have FM Pro hit an internal web app to send the data into SQL that we need, unless anyone can shed some light on this behavior and how to correct it?