When I want to create or alter stored procedures and views, I avoid the "should be the first statement in a batch file" error by putting the sql inside an EXEC statement like this:
Sql(EXEC('Alter View dbo.Foos As etc'))
This is all very well, but it does mean I have to escape apostrophes all the time.
I recently realised that the DbMigration.Sql method takes a boolean parameter named suppressTransaction. It is described like this in the documentation:
A value indicating if the SQL should be executed outside of the transaction being used for the migration process.
so I tested it out without using EXEC :
e.g. Sql('Create View dbo.Foos As etc', true);
It worked, but my concern is this. What if I make a mistake somewhere else in my migrations? I assume everything else gets rolled back but this Sql doesn't. Could I end up with the database in an indeterminate state? If that is true what use is this parameter?
