CODEWITHSUNDEEP
sql-serverado.net
Andrew Bullock
Andrew Bullock

Reputation: 37378

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?

Upvotes: 89

Views: 140246

Answers (9)

Gus Lopez
Gus Lopez

Reputation: 343

In SSMS (SQL Server Management System), you can run GO after any query, but there's a catch. You can't have the semicolon and the GO on the same line. Go figure.

This works:

SELECT 'This Works';
GO

This works too:

SELECT 'This Too'
;
GO

But this doesn't:

SELECT 'This Doesn''t Work'
;GO

Upvotes: 20

Poulad
Poulad

Reputation: 1311

I first tried to remove GO statements by pattern matching on (?:\s|\r?\n)+GO(?:\s|\r?\n)+ regex but found more issues with our SQL scripts that were not compatible for SQL Command executions.

However, thanks to @tim-schmelter answer, I ended up using Microsoft.SqlServer.SqlManagementObjects package.

string sqlText;
string connectionString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=FOO;Integrated Security=True;";
var sqlConnection = new System.Data.SqlClient.SqlConnection(connectionString);
var serverConnection = new Microsoft.SqlServer.Management.Common.ServerConnection(sqlConnection);
var server = new Microsoft.SqlServer.Management.Smo.Server(serverConnection);

int result = server.ConnectionContext.ExecuteNonQuery(sqlText);

Upvotes: 0

Dániel Erős
Dániel Erős

Reputation: 111

I got this error message when I placed the 'GO' keyword after a sql query in the same line, like this:

insert into fruits (Name) values ('Apple'); GO

Writing this in two separate lines run. Maybe this will help someone...

Upvotes: 0

James L.
James L.

Reputation: 14515

I placed a semicolon ; after the GO, which was the cause of my error.

Upvotes: 6

Vistance
Vistance

Reputation: 345

Came across this trying to determine why my query was not working in SSRS. You don't use GO in SSRS, instead use semicolons between your different statements.

Upvotes: 5

Gamic
Gamic

Reputation: 326

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!

Upvotes: 11

Tim Schmelter
Tim Schmelter

Reputation: 460048

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

Upvotes: 32

JotaBe
JotaBe

Reputation: 39004

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.

Upvotes: 120

David Wilson
David Wilson

Reputation: 548

You will also get this error if you have used IF statements and closed them incorrectly.

Remember that you must use BEGIN/END if your IF statement is longer than one line.

This works:

IF @@ROWCOUNT = 0
PRINT 'Row count is zero.'

But if you have two lines, it should look like this:

IF @@ROWCOUNT = 0
BEGIN
PRINT 'Row count is zero.'
PRINT 'You should probably do something about that.'
END

Upvotes: 3

Related Questions