Managing SQL execution within a database using sp_EXECUTESQL failure with 'GO' statement

I have an application which will manage releases of DDL changes and TSql Executable statements to a SQL server database.

Basic workflow: Developer submits .sql file, file is collected, SQL reviewed and assigned a step in a release cycle, release is executed in the database using a stored procedure and it will cycle through the steps executing the SQL within a single SQL transaction using sp_EXECUTESQL. If any errors occur the transaction does not commit th开发者_StackOverflow中文版e DDL changes. This process manages SQL from a single database to multiple database on the same instance.

The problem I have is that when the SQL scripts are submitted they contain "GO" statements which sp_EXECUTESQL does not support and throws the "Incorrect syntax near 'GO'" error. I can split and break up most transactions by parsing on the 'GO' keyword but this will not work for items that are in other databases. Once I have an ALTER of some sort against another database I need the 'GO'. e.g. the following hase ot be executed together, cannot be split and executed as two statements:

USE [MyDatabaseOtherThanOneIAmExecutingFrom]
GO
Alter PROCEDURE [dbo].[DoSomething]
...

Syntactically the following statement won't work so requesting the dev's to change their sql to prefix with a db name would only cover non DDL SQL:

 Alter PROCEDURE [MyDatabaseOtherThanOneIAmExecutingFrom].[dbo].[DoSomething]

Original requirement was to stay within the database to perform these deployment actions so writing a short one off application to execute the batches from .Net using SqlCommand was not an option.

Is there another option to handle this within the database or do I need to extend outside and create an application to manage the SQL steps execution?

You can nest sp_executesql calls. It's a bit ugly, but it works, and allows you to execute DDL against other databases:

sp_executesql N'use OtherDB exec sp_executesql N''create procedure DoStuff @Parm1 varchar(10) as select * from sysobjects'''

As long as you can, by your own words, split and break up most transactions by parsing on the 'GO' keyword, I just can't see the problem.

Just do that and issue the groups of statements between GOs sequentially, i.e. in batches, using the same connection, without interrupting the latter. This is essentially how SSMS, as well as sqlcmd.exe and osql.exe, go about interpreting 'GO' (pardon the pun).

First, you simplest solution is force the split on GO and try to get the sql files to conform to that. Barring that, you could create an SSIS package that passes the file paths to the Execute SQL task and execute it that way. If that doesn't work for you, then your last solution would be to use the SQL Server Management Objects which let you send the entire script to the server en masse. To use the SMO, you'd need to build a small app in something like C# or VB.NET that processes the files and passes the script to the SMO.

Feature Pack for Microsoft SQL Server 2005 - November 2005

(Scroll down to Microsoft SQL Server 2005 Management Objects Collection)