Our legacy process for upgrading our product's databases is built around the use of SQLCMD. We have batch files that execute SQLCMD with -i and -o options and very fragile error handling. I would like to replace this with Powershell and at the same time make it more transactional, such that several script files can be executed sequentially, all in the context of a single transaction. Most of our scripts are generated by SQL Compare, but some are handcrafted. All have GO statements to separate batches and many have PRINT statements also.
The Invoke-Sqlcmd cmdlet from SQLPS does not seem to support any transaction semantics. Is there a way I can affect the connection being used so that I can execute Invoke-Sqlcmd repeatedly within a single transaction? I really want to be able to continue to capture all the output from each script just the way SQLCMD -o or SSMS "Results To text" do.
I tried experimenting directly with SMO objects and ExecuteWithResults and the ability to get messages, result sets, row counts, etc., all into a coherent text stream is difficult at best. (For instance, I have seen examples of declaring event handlers to capture messages, but am at a loss as to how to correctly associate individual messages with any datasets that might be returned.) Is there a way I can accomplish my goals using SMO objects in PowerShell?
- Andy -