My company has a client who is trying to automate application of several scripts to synchronize some of our application's data from one database to another. They have set up a multiple-step SQL Agent job that calls one script per step using sqlcmd. They open a transaction in job step #1 and commit or roll back in the last job step. Each step is set to jump to the last job step in case of step failure. In the last run, they had a step failure that left the first couple of scripts' changes committed and later scripts' changes undone. (rolled back or skipped is immaterial)
The script versions that they have were never intended for automated use and really relied on human intervention when errors happen. (Bad? Yes. I know. result of a no-experienced-person-available-so-let-Joe-do-it decision.) Also the automation was done by the client without asking us if it was do-able using the scripts they have.
My question is this: In this case, is the transaction started in Step One actually in charge of any commits, or do the separate calls to sqlcmd commit when they finish?
