We’ve got a FB project with one of the steps executing a SQL script. Inside the script, it’s wrapped in a transaction, so that if any part of the script errors it logs the error(s) and executes an if-failed statement instead of committing the transaction. This works fine when running the script in SQL Management Studio.
However, when we run the script using the Execute SQL action in FinalBuilder, if the script errors inside the transaction, it does not complete running the script. The error immediately fails the action and bombs out. Is there a way to execute the script as intended without the action breaking?
SQL Server 2017, and we are using the MSQL Execute SQL using sqlcmd. We’ve also turned off the action fail option, which just lets the project continue after it fails - it still bombs out in the middle of the script.
Are you able to try running sqlcmd from the command line and see if it has the same behavior? All FinalBuilder does is run sqlcmd and provide a few options.
Also on the action, try turning on the LogCommandLine option for the action, which can be found on the properties tab. That will help you run it outside of FinalBuilder with the same options.
That is a whole action properties window I did not even know existed and I’ve been using FinalBuilder since v3. Geeze!
Ok, I think we found what we need to do, because it looks like a sqlcmd limitation. It can either execute the full script and not fail out (ExitOnError off) which logs the error but continues on to the next action (undesirable due to dependencies), or it throws the first error and exits the action and fails the run (but doesn’t complete the transaction failure, also undesirable).
