There are problems with "USE" in Sql Azure. If you prefix an SSMS script with “USE {DatabaseName}” to make sure it runs in the right place the statement will either be ignored (you’re already in the database referred to) or raise an error. That will kill the current batch, but if there is a GO statement in the script any statements after it will still run, and of course that will be in the wrong database. What to do?

It was hard to find anything useful on the Net, which is why I decided a blog post might help. What’s needed is a one-liner that works with any script and is effective even when there are several batches (defined by GO statements). I came up with the following:

IF DB_NAME() '{Target Database}' SET NOEXEC ON
GO

It’s not quite a one-liner: statements in the script may need to be at the start of their own batch, so the prefix has to run as a separate one. You may want to add a semi-colon to the first line with a PRINT statement warning the user that NOEXEC is on. You could also reverse the SET statement at the end of the script, but as NOEXEC will only operate on the current script this is somewhat optional. I think that’s it – I’ve used it successfully on an Azure deployment, but if you foresee any problems please comment.