This question already has an answer here:
Note: I believe this differs from other questions simply due to the size of the creation script I want to run under a single transaction.
I'm working on automating the deployment of the data side of an application. I'm working with the team's existing scripts. They seem to drop everything and recreate. I don't have much time to make it perfect.
I'm thinking of running their drop-all script outside of a transaction, but running a single long "genesis" creation script inside a transaction at least so if that part should fail, its at a clean point and easier to recover from.
Genesis.sql contains 34k lines and seems to all be CREAT(ions) of functions, procedures, tables, types etc.
Can I do this:
SET XACT_ABORT ON BEGIN TRAN # Genesis! COMMIT TRAN
What's really confusing everyone is the impact of the GOs in the script with some saying that it will commit the transactions and other people (on the web) saying GO is just an instruction for the client to send a batch.
So far, this hasn't worked, new stuff is written and I see this message:
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
However, I think its mostly due to syntax errors in one or two scripts (that all get concatenated to 30k lines) and the parser is thrown out.
It seems I'll need to dummy run the scripts into an empty, local DB first, to discover syntax errors, then attempt against the target environment server.