GO Statements Blowing Up Sql Execution In .NET

I have a very simple C# command shell app that executes a sql script generated by SQL Server for scripting schema and data. It's blowing up on the 'GO' statements. Error message:

Solution 1:

As others mentioned, split your string by GO statements. But be careful, you may have the text "GO" in other parts of your script. You might also have whitespace before or after the GO statement, and you might have comments on the line after the GO statement also. Any of that would be valid in SSMS, so you may want to test for it.

Here is the method I use:

private static IEnumerable<string> SplitSqlStatements(string sqlScript)
{
    // Make line endings standard to match RegexOptions.Multiline
    sqlScript = Regex.Replace(sqlScript, @"(\r\n|\n\r|\n|\r)", "\n");

    // Split by "GO" statements
    var statements = Regex.Split(
            sqlScript,
            @"^[\t ]*GO[\t ]*\d*[\t ]*(?:--.*)?$",
            RegexOptions.Multiline |
            RegexOptions.IgnorePatternWhitespace |
            RegexOptions.IgnoreCase);

    // Remove empties, trim, and return
    return statements
        .Where(x => !string.IsNullOrWhiteSpace(x))
        .Select(x => x.Trim(' ', '\n'));
}

Solution 2:

If you want to be able to use GO you will need to reference to the following dlls

Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Management.Sdk.Sfc.dll
Microsoft.SqlServer.Smo.dll Microsoft.SqlServer.SqlEnum.dll

Then execute like so

 using (SqlConnection conn = new SqlConnection(connection))
 {
     Server db = new Server(new ServerConnection(conn));
     string script = File.ReadAllText(scriptPath);
     db.ConnectionContext.ExecuteNonQuery(script);      
 }

Solution 3:

GO is not part of SQL, it is something SQL Server Management Studio does for you to split the script up.

What you need to do is read the query in to a string then split on GO on a line by itself (you may want to use Regex for this)

//Its better to dispose the SqlCommand, I also switched constructors so I could re-use the SqlCommand.
using(SqlCommand command = new SqlCommand())
{
    command.Connection = connection;

    var scripts = Regex.Split(script, @"^\w+GO$", RegexOptions.Multiline);
    foreach(var splitScript in scripts)
    {
        command.CommandText = splitScript;
        command.ExecuteNonQuery();
    }
}

Look at Matt Johnson's answer for a less naive implementation of the GO splitting.

Solution 4:

GO is not a valid QA command, it is a batch separator... It is processed by Enterprise Manager to separate SQL scripts. As such, it will work in Enterprise Manager, but not in database calls from C# or other external programs....

Solution 5:

As mentioned in another answer, GO is not supported.

You can use String.Split() on your script using your GO statements as delimiters, and run each segment as a command, separately.