In the middle of a project you might want to automate DB restoring and upgrading by means of existing SQL scripts which may contain GO-statements. If you are that person who is attempting to execute scripts at runtime with GO-statements, this article describes how to do that.
The proposed solution is a workaround. Personally I’d rewrite all scripts to execute without any issues automatically.
I created several examples of how the statements can be executed. The sample code is stored on GitHub. Make sure <startup> element in your *.config file has the attribute useLegacyV2RuntimeActivationPolicy=”true”. This prevents the error “Mixed mode assembly is built against version ‘v2.0.50727’ of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information.” More details here.
SMO
Most likely, your first thought was that SQL Server Management Objects (SMO) should help with the task. Unfortunately, it’s not. Since the Go-statement is not a part of T-SQL language the SMO script runner removes them and splits all SQL scripts by GO statement. Later they are executed separately one by one. In general the logic of such scripts can assume that it would be executed as a whole at once. You can see how it might look like in SQL Profile for these script.
You may try it out on yourself. SMO assemblies are located at my PC here c:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies\
- Microsoft.SqlServer.Smo
- Microsoft.SqlServer.ConnectionInfo
- Microsoft.SqlServer.Management.Sdk.Sfc
Solutions
The GO-statement is not a Transact-SQL statement as the article on MSDN says: “GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor”.
The utility osql is obsolete and according to MS we should not consider it in any solutions.
There are two other options: using sqlcmd and patching SQL batch to remove all GO injections.
Patching of SQL is a dynamic composition of a statement to be executed with EXECUTE statement. All GO statements should be replaced with empty string in such a case.
Running Sqlcmd requires calling to OS routings and might not be convenient. Still the example can be found on MSDN.
THANK YOU!!!!!! You saved my life. I’ve seen thousands of article on this issue, but no help.