Disable transaction on subset of scripts

[ben-purcell]

Hi there,

I am using the dbup cli on a large project which works fine for the most part. I am using the option to run each script inside its own transaction.

I've just come across a problem, one of our scripts is required to run outside of a transaction. From what I can tell, my options are:

1. Run without transactions and add the transaction boilerplate to each script. Kind of annoying to have to change for just 1 script, because the current setup works fine for the rest of our scripts.
2. Put the "non-transaction" scripts in another folder and run them separately - only problem is that some of the transaction scripts may depend on the "non-transacton" scripts.

Any ideas or workarounds? FYI the statement that cannot go inside a transaction is for SQL Server:

CREATE FULLTEXT CATALOG ...

[huesie]

Hi Ben - (not tried this but it's a method I use in sqltestio) - can you capture @@TRANCOUNT; issue correct number of COMMIT TRAN (or a single ROLLBACK TRAN); and then issue the correct number of BEGIN TRAN before leaving the script?

You could do feasibility with just a COMMIT TRAN at the top and a BEGIN TRAN at the end which may be a simple thing to try.

Hue.

[ben-purcell]

Thanks for responding Hue. This is a good idea but no luck I'm afraid. I think the C# code that runs the scripts references a specific transaction and complains when it is committed/rolled back:

Just thinking about a feature that could be implemented to solve this problem, if we could do something like this in the .yml:

scripts:
    -   folder:  ./NonTransactional             # absolute or relative (to this file) path to a folder with *.sql files
        transaction: PerScript  # **The ability to override this in subfolder level!**
        order: 100              # script group order, default 100

Then you would be able to put scripts that have different transaction config than the rest of your scripts in a separate subfolder. I suppose this wouldn't be compatible with the option to wrap everything in one big transaction, but would work ok with the no transaction/transaction per script options.