I'm a little confused... If you have ESS going then you don't need the "Execute SQL" script step for updates and inserts. You can do it natively in FM.
If you do not have an ESS connection then you can use the "Exeucte SQL" script step on the server through "Perform Script on Server" and use just one DSN that has been set up on the server instead of setting up that ODBC driver / DSN setup on each workstation.
That's one of the big sleeper functions in FM13.
Aha! So it seems my biggest issue is needing to upgrade from 12 to 13 so that I can get access to the server's DSN. We'd been holding out for the first patch, but I'll push that forward as soon as possible now.
With the ESS setup, creating records in the SQL table using FileMaker functions (New Record and Copy Record) caused errors. Specifically, I was getting the message "The target table '<<name of table>>' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause." My assumption is that there are rules in place on the SQL side of the system to protect itself. Using Execute SQL provided whatever was missing from the way FileMaker was creating the record, giving me the power to create a record without the error.
I'm also looking forward to bundling the deletion of records from multiple SQL tables into a single step. For example, the employee system in the accounting software uses a main table and several supporting tables. The main table will not allow deletion if there are related records in the supporting tables, so my SQL query deletes from all supporting tables first and then the main table. Doing this with one Execute SQL step, in the same manner I do it in one query in the SQL Management Studio, should give me the "all or nothing" process I've enjoyed. I have not specifically tested this point yet, since record creation was the top priority (there should be very few cases where deletion is needed), and I had not yet hammered that out to a smooth finish.
Thank you very much for the FMS13 idea, and I'll report back once we have it up and running! I'll also be keeping my eye on this discussion if there are any other questions, comments, and/or ideas.
Message was edited by: james_ludwig to mention the current system is on FMS12
As Wim mentioned, the combination of Execute Script on Server and the old Execute SQL Script step provides some great functionality. I'm a little confused as to why your native ESS inserts and deletes don't work. Are you running against Views vs. regular tables by chance? What SQL DB are you using?
There's no problem with not using ESS, it can often be faster, especially calling stored procedures, but your basic ESS functions should work barring some really odd triggers.
Also, FM13 has in our experience been very solid so far. Especially FM Pro. WebDirect has been decent as a first release and I think we will see improvements there with patch releases. If you were burnt by the intial release of FM12, FM13 is magnitudes more solid, IMO.
User19752 hit it on the nose. The tables I'm hitting have triggers on them.
We're using Deltek's "Vision" database for accounting purposes. It integrates much better with our project based business than QuickBooks did, but the functionality of the project portion can't come anywhere near what we're used to in our FileMaker database. As a result, we're pushing the project (and supporting) information up to Vision so that the accounting department doesn't have to duplicate it for their work and our users can stay on the system they've grown to love.
Thanks for your input on 13 being solid. I'll be upgrading the server this weekend, and testing the new step all next week. We actually had to wait quite a while before we could move to 12 because of the file extension change, so it looks like we missed the burn.
Following up on everything...
PSOS is definitely the saving grace for the development. Developers will need to have a mirror of the ODBC connection on their system to make the initial Execute SQL step, but it can then be copied and reused throughout the system as needed.
After looking up the chatter about PSOS performance benchmarking, I'm much more comfortable with having these little record creation scripts running as often as they'll need to in order to keep the two systems reasonably synced. I'm also happy to have seen Wim's statement out there about the FMS setting for max concurrent PSOS sessions. That limit of 25 would have tripped me up quickly with over 100 users signed in at any time. Also of note for anyone out there first using PSOS, you must provide the full context/perspective as you would for a scheduled script. I know a lot of other people have said this, but it doesn't hurt to say it again.
A huge thanks to everyone for your help in leading myself and others onto the right path. If anyone encounters this thread and would like to discuss my experience/solution/development, I'll be happy to talk.
So how many users to you have in your system now?
What is this limit of 25 for PSOS sessions? This isn't something I was aware of.
Lee: it's a setting in the FMS admin console. The default is 25, max is 500.
That went right by me. I'm sure I bumped it up. Glad it's not a hard default.