Executing batch T-SQL Scripts containing GO statements throws exception

Question

I am trying to execute SQl Scripts cotaining GO Statements with following code

SqlConnection sqlConnection = new SqlConnection(RConnString);
ServerConnection svrConnection = new ServerConnection(sqlConnection);
Server server = new Server(svrConnection);
returnvalue = server.ConnectionContext.ExecuteNonQuery(strSpScript);
return Convert.ToString(returnvalue);

But it throws following excetpion on live. this code works fine in my local pc

Exception has been thrown by the target of an invocation. at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at Microsoft.SqlServer.Management.Common.ServerConnection.GetStatements(String query, ExecutionTypes executionType, Int32& statementsToReverse) at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType) at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand)

GO is a command that client tools are meant to use to know to split the script into separate batches and send the batches to SQL Server individually. You're acting in the role of a client tool now so it's up to your code to perform that same task - find the GOs (being careful around quoted and commented text) - and execute each batch separately. — Damien_The_Unbeliever

Sam Sam · Accepted Answer · 2014-06-25T06:52:36

Use code like the following:

using (var connection = new SqlConnection(RConnString)) 
{
    connection.Open();
    foreach (var batch in strSpScript.Split(new string[] {"\nGO", "\ngo"}, StringSplitOptions.RemoveEmptyEntries))
    {
         try
         {
             new SqlCommand(batch, connection).ExecuteNonQuery();
         }
         catch (Exception ex)
         {
             Console.WriteLine(ex.Message);
             throw;
         }
    }
}

This will only work if you have 'GO' on a newline (and in consistent case) - modify as necessary.

Executing batch T-SQL Scripts containing GO statements throws exception

2 Answers