Using "GO" within a transaction

Question

I'm building a web app that attempts to install/upgrade the database on App_Start. Part of the installation procedure is to ensure that the database has the asp.net features installed. For this I am using the System.Web.Management.SqlServices object.

My intention is to do all the database work within an SQL transaction and if any of it fails, roll back the transaction and leave the database untouched.

the SqlServices object has a method "Install" that takes a ConnectionString but not a transaction. So instead I use SqlServices.GenerateApplicationServicesScripts like so:

string script = SqlServices.GenerateApplicationServicesScripts(true, SqlFeatures.All, _connection.Database);
SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, script, ...);

and then I use the SqlHelper from the Enterprise Library.

but this throws an Exception with a butt-load of errors, a few are below

Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near the keyword 'USE'.
Incorrect syntax near the keyword 'CREATE'.
Incorrect syntax near 'GO'.
The variable name '@cmd' has already been declared. Variable names must be unique within a query batch or stored procedure.

I'm presuming it's some issue with using the GO statement within an SQL Transaction.

How can I get this generated script to work when executed in this way.

Solution 1

GO is not a SQL keyword.

It's a batch separator used by client tools (like SSMS) to break the entire script up into batches

You'll have to break up the script into batches yourself or use something like sqlcmd with "-c GO" or osql to deal with "GO"

Solution 2

I just want to add that if you name your transaction, you can include multiple GO sections within in and they will all roll back as a unit. Such as:

BEGIN TRANSACTION TransactionWithGos;
GO

SET XACT_ABORT ON; -- Roll back everything if error occurs in script
GO

-- do stuff
GO

COMMIT TRANSACTION TransactionWithGos;
GO

Solution 3

Poor man's way to fix this: split the SQL on the GO statements. Something like:

private static List<string> getCommands(string testDataSql)
{
    string[] splitcommands = File.ReadAllText(testDataSql).Split(new string[]{"GO\r\n"}, StringSplitOptions.RemoveEmptyEntries);
    List<string> commandList = new List<string>(splitcommands);
    return commandList;
}

[that was actually copied out of the app I am working on now. I garun-freaking-tee this code]

Then just ExecuteNonQuery() over the list. Get fancy and use transactions for bonus points.

# # # # # BONUS POINTS # # # # # #

How to handle the transaction bits really depends on operational goals. Basically you could do it two ways:

a) Wrap the entire execution in a single transaction, which would make sense if you really wanted everything to either execute or fail (better option IMHO)

b) Wrap each call to ExecuteNonQuery() in its own transaction. Well, actually, each call is its own transaction. But you could catch the exceptions and carry on to the next item. Of course, if this is typical generated DDL stuff, oftentimes the next part depends on a previous part so one part failing will probably bugger the whole pooch.

Solution 4

EDIT as pointed out below I specified tran where batch was actually correct.

The issue is not that you cannot use GO within a transaction so much as GO indicates the end of a batch, and is not itself a T-SQL command. You could execute the whole script using SQLCMD, or split it on the GO's and execute each batch in turn.

Solution 5

test your scripts:

• restore a production backup on a backup server
• run run scripts with GOs

install your scripts:

• shut down application
• go to single user mode
• backup database
• run scripts with GOs, on failure restore backup
• go back to multi user mode
• restart application

View more solutions

Comments

[DJ.]

Don't confuse transactions and batches - the go statements specify a batch of commands which may include one or more database transactions

[KM.]

Down vote this if you like, but you won't get multiple GOs in a single transaction. Based on the OP error, there are CREATEs in the script, which will require GOs. The only way to rollback if something goes wrong partway is this method.

[KM.]

how will you rollback changes if there is an error in one section of the script?

[Beska]

I'm not clear on why this was downvoted. It's certainly a lot of work, but it seems to hit the idea, and allow rollbacks (unlike some of the other methods...)

[Wyatt Barnett]

Downvoted b/c it doesn't answer the OP's question--like trying to run this within the application. Also, taking apps offline is so ugly.

[ZygD]

SET XACT_ABORT ON clever handling, like Red Gate does.

[Greg B]

I plan on using a single transaction accross all statements to rollback the entire process. Cheers!

[ZygD]

To be fair, KM's solution is 100% accurate and the safest. I only use proper differential tools and proper SQL client tools. For 3rd party apps, I don't recall ever seeing OP's solution to upgrade a databases: it's always SQL scripts and batch files.

[KM.]

@Wayatt Barnett said "Also, taking apps offline is so ugly.", messing up the database with a failed script that can not be rolled back, while it is up and running, "is so ugly"

[Greg B]

Hi KM, My problem was that I needed to create an installable application that would not require the installing user to go near the database. simply specifiy a connection string and install or fail with a usefull error

[pilavdzice]

This does not work very well. Even simple scripts that just ALTER VIEW or something require being in their own BATCH.

[pilavdzice]

This only works in SQL Server Management Studio and other such tools, it is not valid SQL so this does not work in C# for example.

[CodeGrue]

I was assuming you would have this is a stored procedure.

[eka]

We use GO to separate batches, say between drop and create, or to start new batch where we use CREATE/ALTER. removing GO will result in another error CREATE/ALTER should be the first statement in the batch

[Mark Whitfeld]

Hi @eka thanks for the feedback. I am fully aware of the shortcomings of this solution and have stated this in my post. I'm not sure why that merits a downvote though.

[AFract]

The previous answer, despite it has been Accepted, looks like a bit unclear to me. So I would like to add a relevant precision : IMHO there is absolutely no problem with wrapping transactions other multiples batches. So you can perfectly put "GO" within a transaction.

[Darrell]

The above could split sql scripts incorrectly if you have for example, comments containing the word GO (followed by a new line) etc - so use carefully. Alternatively, you can use something like the following, which is a StringReader implementation, that will read and split the SQL, and is sensitive to comments etc: github.com/DbUp/DbUp/blob/master/src/DbUp/Support/SqlServer/‌​…

[Wyatt Barnett]

@Darrell -- that is certainly the case, mainly was used for dealing with SSMS generated SQL that had no comments and plenty of GO statements. Luckily the world has progressed passed that in recent years.

[Bizhan]

This is genius! clean and fast

[Mauro]

NOTE: This example will NOT work because after GO SET XACT_ABORT returns to OFF