title: Flyway fail on error in Transact-SQL migration
body: <p>When using Flyway in combination with a Microsoft SQL Server, we are observing the issue described on <a href="https://stackoverflow.com/q/64563382/5127499" title="Transact-SQL transaction rollback not working properly when using GO commands">this question</a>.</p>
  <p>Basically, a migration script like this one does not rollback the successful <code>GO</code>-delimited batches when another part failed:</p>
  <pre class="lang-sql prettyprint-override"><code>BEGIN TRANSACTION
  
  -- Create a table with two nullable columns
  CREATE TABLE [dbo].[t1](
      [id] [nvarchar](36) NULL,
      [name] [nvarchar](36) NULL
  )
  
  -- add one row having one NULL column
  INSERT INTO [dbo].[t1] VALUES(NEWID(), NULL)
  
  -- set one column as NOT NULLABLE
  -- this fails because of the previous insert
  ALTER TABLE [dbo].[t1] ALTER COLUMN [name] [nvarchar](36) NOT NULL
  GO
  
  -- create a table as next action, so that we can test whether the rollback happened properly
  CREATE TABLE [dbo].[t2](
      [id] [nvarchar](36) NOT NULL
  )
  GO
  
  COMMIT TRANSACTION
  </code></pre>
  <p>In the above example, the table <code>t2</code> is being created even though the preceding <code>ALTER TABLE</code> statement fails.</p>
  <p>On the linked question, the following approaches (outside of the flyway context) were suggested:</p>
  <ol>
  <li>
  <blockquote>
  <p>A multi-batch script should have a single error handler scope that rolls back the transaction on error, and commits at the end. In TSQL you can do this with dynamic sql</p>
  </blockquote>
  <ul>
  <li><em>Dynamic SQL makes for hard-to-read script and would be very inconvenient</em></li>
  </ul>
  </li>
  <li>
  <blockquote>
  <p>With SQLCMD you can use the <code>-b</code> option to abort the script on error</p>
  </blockquote>
  <ul>
  <li><strong>Is this available in flyway?</strong></li>
  </ul>
  </li>
  <li>
  <blockquote>
  <p>Or roll your own script runner</p>
  </blockquote>
  <ul>
  <li><strong>Is this maybe the case in flyway? Is there a flyway-specific configuration to enable proper failing on errors?</strong></li>
  </ul>
  </li>
  </ol>
  <hr />
  <p><em>EDIT: alternative example</em></p>
  <h3>Given: simple database</h3>
  <pre class="lang-sql prettyprint-override"><code>BEGIN TRANSACTION
  
  CREATE TABLE [a] (
      [a_id] [nvarchar](36) NOT NULL,
      [a_name] [nvarchar](100) NOT NULL
  );
  
  CREATE TABLE [b] (
      [b_id] [nvarchar](36) NOT NULL,
      [a_name] [nvarchar](100) NOT NULL
  );
  
  INSERT INTO [a] VALUES (NEWID(), 'name-1');
  INSERT INTO [b] VALUES (NEWID(), 'name-1'), (NEWID(), 'name-2');
  
  COMMIT TRANSACTION
  </code></pre>
  <h3>Migration Script 1 (failing, without GO)</h3>
  <pre class="lang-sql prettyprint-override"><code>BEGIN TRANSACTION
  
  ALTER TABLE [b] ADD [a_id] [nvarchar](36) NULL;
  
  UPDATE [b] SET [a_id] = [a].[a_id] FROM [a] WHERE [a].[a_name] = [b].[a_name];
  
  ALTER TABLE [b] ALTER COLUMN [a_id] [nvarchar](36) NOT NULL;
  
  ALTER TABLE [b] DROP COLUMN [a_name];
  
  COMMIT TRANSACTION
  </code></pre>
  <p>This results in the error message <code>Invalid column name 'a_id'.</code> for the <code>UPDATE</code> statement.<br />
  Possible solution: introduce <code>GO</code> between statements</p>
  <h3>Migration Script 2 (with GO: working for &quot;happy case&quot; but only partial rollback when there's an error)</h3>
  <pre class="lang-sql prettyprint-override"><code>BEGIN TRANSACTION
  SET XACT_ABORT ON
  GO
  
  ALTER TABLE [b] ADD [a_id] [nvarchar](36) NULL;
  GO
  UPDATE [b] SET [a_id] = [a].[a_id] FROM [a] WHERE [a].[a_name] = [b].[a_name];
  GO
  ALTER TABLE [b] ALTER COLUMN [a_id] [nvarchar](36) NOT NULL;
  GO
  ALTER TABLE [b] DROP COLUMN [a_name];
  GO
  
  COMMIT TRANSACTION
  </code></pre>
  <ul>
  <li>This performs the desired migration as long as all values in table <code>[b]</code> have a matching entry in table <code>[a]</code>.</li>
  <li>In the given example, that's not the case. I.e. we get two errors:
  <ul>
  <li>expected: <code>Cannot insert the value NULL into column 'a_id', table 'test.dbo.b'; column does not allow nulls. UPDATE fails.</code></li>
  <li>unexpected: <code>The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.</code></li>
  <li><strong>Horrifyingly: the last <code>ALTER TABLE [b] DROP COLUMN [a_name]</code> statement was actually executed, committed and not rolled back.</strong> I.e. one cannot fix this up afterwards as the linking column is lost.</li>
  </ul>
  </li>
  </ul>
  <p>This behaviour is actually independent of flyway and can be reproduced directly via SSMS.</p>