Issues

Issue #97 new

MSSQL ddl implementation should perhaps issue a final GO at the end of the batch?

Derek Harland
created an issue

Currently if invoked with --sql alembic will generate sql appearing as

BEGIN TRANSACTION;

<some ddl>
GO

...

<some ddl>
GO

UPDATE alembic_version SET version_num='<new version>';
GO

COMMIT;

Depending on the tool that is used to apply this file, it is possible that the final COMMIT will not actually be issued as the tool may not issue an implicit GO at the end-of-file.

(The offending tool I've observed is sqsh).

Comments (4)

  1. Mike Bayer repo owner

    but you don't need it after the BEGIN TRANSACTION ? We've been feeding SQL Server files generated here into the regular enterprise manager tool or whatever it's called, we needed the GO after the statements but AFAIK not the commit.

    but this is very trivial and harmless so I've no issue here.

    this is a patch which could benefit from a unit test in test_mssql:

    diff -r 094fb87017517ac5e60ad0df030ec574314611f7 alembic/ddl/mssql.py
    --- a/alembic/ddl/mssql.py  Sat Jan 05 13:27:29 2013 -0500
    +++ b/alembic/ddl/mssql.py  Thu Jan 10 13:04:26 2013 -0500
    @@ -24,6 +24,11 @@
         def emit_begin(self):
             self.static_output("BEGIN TRANSACTION" + self.command_terminator)
    
    +    def emit_commit(self):
    +        super(MSSQLImpl, self).emit_commit()
    +        if self.as_sql and self.batch_separator:
    +            self.static_output(self.batch_separator)
    +
         def alter_column(self, table_name, column_name,
                             nullable=None,
                             server_default=False,
    
  2. Derek Harland reporter

    (Reply via dere...@finq.co.nz):

    There's no issue with the BEGIN TRANSACTION as its allowed to be mixed with other statements in a batch ... the intervening GOs are only needed due to mssql's archaic rules about ddl needing to be issued separately from each other.

    The problem seems to be that eg sqsh (which is a transact-sql shell on unix) determines batches to issue by splitting on the batch separator but demanding a terminating separator to delimit the final batch. Whereas eg mssql's sqlcmd assumes an implicit one at file end.

    I can fork and add a test if you like (and include your patch).

  3. Log in to comment