sql-serverflyway

How to disable transactions for individual migrations in Flyway on SQL Server

I have a Flyway migration that is failing in Flyway Community Edition 6.0.8 with the following error:

The procedure 'sys.sp_grantdbaccess' cannot be executed within a transaction.

My original migration script is:

EXEC sp_grantdbaccess 'foo', 'bar'

I attempted to run it as dynamic SQL but ran into the same issue:

exec('EXEC sp_grantdbaccess N''foo'', N''bar''')

I have also tried the -mixed=true option on the flyway migrate command line to allow transactional and non-trnsactional migrations to run but I am receiving the same error.

Can transactions be disabled on a per-migration basis? Or even for an entire "migrate" operation? If neither of those are options, is there a way within the migration script itself to effect the change in question without running into the transaction restriction?

This is not a duplicate of Is there any way to disable flyway SQL migration from the transaction for several reasons:

  1. The error occurs for a different SQL operation.
  2. The behavior in question appears to have been resolved in an earlier release of Flyway, but is still occurring for this operation.
Solution

  • The ability to configure individual scripts to (not) run in a transaction has been added in Flyway 6.1: https://documentation.red-gate.com/flyway/reference/script-configuration