GO statements blowing up sql execution in .NET

Question

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:

Incorrect syntax near 'GO'.

Here is the full sql script:

/****** Object:  Table [gym].[MembershipStatus]    Script Date: 9/3/2013 9:24:01 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [gym].[MembershipStatus](
    [MembershipStatusID] [tinyint] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](75) NOT NULL,
    [Description] [varchar](400) NOT NULL,
    [AllowCheckin] [bit] NOT NULL,
    [IncludeInCollections] [bit] NOT NULL,
    [ScheduleFutureInvoices] [bit] NOT NULL,
 CONSTRAINT [MembershipStatus_PK] PRIMARY KEY CLUSTERED 
(
    [MembershipStatusID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [gym].[MembershipStatus] ON 

INSERT [gym].[MembershipStatus] ([MembershipStatusID], [Name], [Description], [AllowCheckin], [IncludeInCollections], [ScheduleFutureInvoices]) VALUES (1, N'Active', N'Active', 1, 1, 1)
INSERT [gym].[MembershipStatus] ([MembershipStatusID], [Name], [Description], [AllowCheckin], [IncludeInCollections], [ScheduleFutureInvoices]) VALUES (2, N'Cancelled', N'Cancelled', 0, 1, 0)
INSERT [gym].[MembershipStatus] ([MembershipStatusID], [Name], [Description], [AllowCheckin], [IncludeInCollections], [ScheduleFutureInvoices]) VALUES (3, N'Collection', N'Collection', 0, 0, 0)
INSERT [gym].[MembershipStatus] ([MembershipStatusID], [Name], [Description], [AllowCheckin], [IncludeInCollections], [ScheduleFutureInvoices]) VALUES (4, N'Deleted', N'Deleted', 0, 0, 0)
INSERT [gym].[MembershipStatus] ([MembershipStatusID], [Name], [Description], [AllowCheckin], [IncludeInCollections], [ScheduleFutureInvoices]) VALUES (5, N'Expired', N'Expired', 1, 1, 1)
INSERT [gym].[MembershipStatus] ([MembershipStatusID], [Name], [Description], [AllowCheckin], [IncludeInCollections], [ScheduleFutureInvoices]) VALUES (6, N'Freeze', N'Freeze', 0, 1, 0)
INSERT [gym].[MembershipStatus] ([MembershipStatusID], [Name], [Description], [AllowCheckin], [IncludeInCollections], [ScheduleFutureInvoices]) VALUES (7, N'Inactive', N'Inactive', 0, 1, 1)
SET IDENTITY_INSERT [gym].[MembershipStatus] OFF
ALTER TABLE [gym].[MembershipStatus] ADD  DEFAULT ('') FOR [Name]
GO
ALTER TABLE [gym].[MembershipStatus] ADD  DEFAULT ('') FOR [Description]
GO
ALTER TABLE [gym].[MembershipStatus] ADD  DEFAULT ((0)) FOR [AllowCheckin]
GO
ALTER TABLE [gym].[MembershipStatus] ADD  DEFAULT ((0)) FOR [IncludeInCollections]
GO
ALTER TABLE [gym].[MembershipStatus] ADD  DEFAULT ((0)) FOR [ScheduleFutureInvoices]
GO

The relevant section of my code looks like this:

SqlCommand command = new SqlCommand(script, connection);
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();

Any ideas?

Answer 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'));
}

Answer 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);      
 }

Answer 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.

Answer 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....

Answer 5

This answer was very helpful, but ultimately did not work for me, as-is. I have a .NET Framework 6.0 project. Those dll's didn't work.

To get this code working:

using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

Server db = new Server(new ServerConnection(conn));
db.ConnectionContext.ExecuteNonQuery(sql);

I needed to install Microsoft.SqlServer.SqlManagementObjects using NuGet (161.47021.0)

Then, SQL with GO executed without error

Answer 6

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.

Answer 7

As an alternative to massaging the scripts to make them runnable through C#, you could just run them as-is by using the sqlcmd utility. Lot of details at:

http://technet.microsoft.com/en-us/library/ms180944.aspx

By using sqlcmd, you can script out the execution of any number of your SQL Server generated scripts, without stripping out the Go statements.

Answer 8

string[] commands = sql.Split( 
    new string[]{"GO\r\n", "GO ", "GO\t"}, StringSplitOptions.RemoveEmptyEntries );
foreach (string c in commands)
{
    command = new SqlCommand(c, masterConnection);
    command.ExecuteNonQuery();
}
}
catch (Exception e)
{
    MessageBox.Show(e.Message);
}
finally
{
    masterConnection.Close();
}
}

Found here. http://blogs.msdn.com/b/onoj/archive/2008/02/26/incorrect-syntax-near-go-sqlcommand-executenonquery.aspx

Answer 9

The top answer has a mistake. I just tested a working solution: You should allow space,';' or new line before GO

            var scripts = Regex.Split(statementText, @"(\s+|;|\n|\r)GO", RegexOptions.Multiline);
            foreach(var splitScript in scripts.Where(splitScript => !splitScript.IsNullOrWhiteSpace())) {
                cmd.CommandText = splitScript;
                cmd.ExecuteNonQuery();
            }

Answer 10

1 additional point to "iamkrillin"'s answer, for using the old DLLs to make it work.

after adding the references to these DLLs

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

from a place like this: "C:\Program Files (x86)\Microsoft SQL Server\130\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll" to the project, I needed to add the following "using" directives to the top of my code file:

using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

....
string query = @" //sql multi-command text here"

using (SqlConnection thisconn = new SqlConnection(connectionString)) {
    Server db = new Server(new ServerConnection(thisconn));
    db.ConnectionContext.ExecuteNonQuery(query);
}

Answer 11

There is very nice SqlServerBatchParser class inside FluentMigrator library

Supports GO GO 3 statements.

You need to install FluentMigrator.Runner.SqlServer nuget package

Example generously copied(slightly modified) from FluentMigrator source code:

using FluentMigrator.Runner.BatchParser;
using FluentMigrator.Runner.BatchParser.SpecialTokenSearchers;
using FluentMigrator.Runner.BatchParser.Sources;

void Main()
{
    var connString = "Server=.;Database=mydb;Trusted_Connection=True;";
    var sql = @"select 1;
GO
SELECT 2;
GO 5";
    ExecuteBatchNonQuery(connString, sql);
}

public static void ExecuteBatchNonQuery(string ConnectionString, string sql)
{
    var sqlBatch = string.Empty;
    var conn = new SqlConnection(ConnectionString);
    conn.Open();

    try
    {
        var parser = new SqlServerBatchParser();
        parser.SqlText += (sender, args) => { sqlBatch = args.SqlText.Trim(); };
        parser.SpecialToken += (sender, args) =>
        {
            if (string.IsNullOrEmpty(sqlBatch))
                return;

            if (args.Opaque is GoSearcher.GoSearcherParameters goParams)
            {
                using (var command = conn.CreateCommand())
                {
                    command.CommandText = sqlBatch;

                    for (var i = 0; i != goParams.Count; ++i)
                    {
                        command.ExecuteNonQuery();
                    }
                }
            }

            sqlBatch = null;
        };

        using (var source = new TextReaderSource(new StringReader(sql), true))
        {
            parser.Process(source, stripComments: true);
        }

        if (!string.IsNullOrEmpty(sqlBatch))
        {
            using (var command = conn.CreateCommand())
            {
                command.CommandText = sqlBatch;
                command.ExecuteNonQuery();
            }
        }
    }
    catch (Exception ex)
    {
        using (var message = new StringWriter())
        {
            message.WriteLine("An error occured executing the following sql:");
            message.WriteLine(string.IsNullOrEmpty(sqlBatch) ? sql : sqlBatch);
            message.WriteLine("The error was {0}", ex.Message);

            throw new Exception(message.ToString(), ex);
        }
    }
    finally
    {
        conn?.Dispose();
    }
}

Credits: https://github.com/fluentmigrator/fluentmigrator/blob/v3.2.1/src/FluentMigrator.Runner.SqlServer/Processors/SqlServer/SqlServerProcessor.cs

Answer 12

Just replace "GO" with "" and it works.

SqlCommand command = new SqlCommand(script.Replace("GO", ""), connection);
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();

Answer 13

I had this issue back when I started coding in C# I have used and tested all possible answers but non led to the answer I loved. Finally I found a good way to do it without using any regex or third-party frameworks. many people here are having plausible answers but none worked for me, here is how I did it:

   string FileUploaded = File.ReadAllText(filePath);
   string[] fileSplited = FileUploaded.Split(new string[] { "GO" }, StringSplitOptions.None);

   using (SqlConnection conn = new SqlConnection(connStr))
   using (SqlCommand cmd = new SqlCommand())
   {
                        
       cmd.Connection = conn;
       conn.Open();

       foreach (string str in fileSplited)
       {
            cmd.CommandText = str;
            cmd.ExecuteNonQuery();
       }                 
   }

yo can also use try-catch block to help you debug better. AS you can see this is so similar to all the answers above but the only difference is the StringSplitOptions.None option that I see no one mentioned

Hope it helps

Answer 14

I know this question is a little old but I thought I'd add another way to do this via the sqlcmd utility using System.Diagnostics.Process.

public void ExecuteSQLFromFile(string filePath, string sqlInstance)
{
    if(File.Exists(filePath))
    {
        var p = new Process();
        p.StartInfo.Verb = "runas";
        p.StartInfo.RedirectStandardOutput = true;
        p.StartInfo.RedirectStandardError = true;
        p.StartInfo.UseShellExecute = false;
        p.StartInfo.FileName = "sqlcmd";
        p.StartInfo.Arguments = "-S " + sqlInstance + " -i " + filePath;
        p.Start();

        var standardOutput = p.StandardOutput.ReadToEnd();
        var standardError = p.StandardError.ReadToEnd();
        p.WaitForExit();
    }
}

Link to learn.ms.com article with sqlcmd downloads and usage.