Script-Migration for SQL Server does not script transactions correctly

[rbonomo]

Script-Migration for SQL Server tries to wrap all operations in a transaction, but also runs GO after every operation within the transaction, which is the issue. Each operation should not have GO after it when everything is wrapped in a transaction. The transaction is not processed correctly because of the GO commands.

_No, you cannot use the GO statement inside a transaction in T-SQL because GO is not a T-SQL command itself. It is a batch separator used by client tools like SQL Server Management Studio (SSMS) and SQLCMD to separate blocks of code to be sent to the server in separate batches. When SQL Server encounters GO, it treats the preceding block of code as a complete batch and sends it to the server for execution.

Once you use GO, the transaction is automatically committed if it was open and has not been rolled back. If you try to use GO within a transaction block, it will split the transaction, and the server will process the batches independently.

To keep a transaction intact, you should avoid using GO and instead execute the entire transaction as a single batch without batch separators._

BEGIN TRANSACTION
    -- Some transactional statements
GO  -- Invalid use of GO in a transaction
    -- More transactional statements
COMMIT