Incorrect syntax near 'GO'

How can I execute the following SQL inside a single command (single execution) through ADO.NET?

ALTER TABLE [MyTable]
    ADD NewCol INT

GO

UPDATE [MyTable] 
    SET [NewCol] = 1

The batch separator GO is not supported, and without it the second statement fails.

Are there any solutions to this other than using multiple command executions?

Solution 1:

The GO keyword is not T-SQL, but a SQL Server Management Studio artifact that allows you to separate the execution of a script file in multiple batches.I.e. when you run a T-SQL script file in SSMS, the statements are run in batches separated by the GO keyword. More details can be found here: https://msdn.microsoft.com/en-us/library/ms188037.aspx

If you read that, you'll see that sqlcmd and osql do also support GO.

SQL Server doesn't understand the GO keyword. So if you need an equivalent, you need to separate and run the batches individually on your own.

Solution 2:

Remove the GO:

String sql = "ALTER TABLE  [MyTable] ADD NewCol INT;";
cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
sql = "UPDATE [MyTable] SET [NewCol] = 1";
cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();

It seems that you can use the Server class for that. Here is an article:

C#: Executing batch T-SQL Scripts containing GO statements

Solution 3:

This can also happen when your batch separator has been changed in your settings. In SSMS click on Tools --> Options and go to Query Execution/SQL Server/General to check that batch separator.

I've just had this fail with a script that didn't have CR LF line endings. Closing and reopening the script seems to prompt a fix. Just another thing to check for!