5

I am using SSMS v17.6 with a SQL Server Express v14.0.1000.169.

When I run the following DELETE statement:

delete from foo
go

I get the error:

Incorrect syntax near 'go'.

But if I execute a similar SELECT statement:

select * from foo 
go

Then there is no error.

It seems the error happens with any statement that doesn't return results. I have checked the query execution settings in SSMS and the batch separator is set to go. It seems SSMS is sending to the server which it shouldn't do.

I have seen this on multiple machines.

pim
  • 12,019
  • 6
  • 66
  • 69
Shane
  • 2,271
  • 3
  • 27
  • 55
  • 2
    Good question +1. I wonder if all DML statements would behave this way. In any case, though semicolon is part of the ANSI-92 standard, it wasn't fully adopted by SQL Server, hence it seems it has its own quirky set of rules. – Tim Biegeleisen Apr 06 '18 at 05:07
  • Hm... seems to be version specific since I couldn't reproduce the error - neither in SSMS nor in SQL Fiddle: http://sqlfiddle.com/#!18/09ab8/1 ... – Tyron78 Apr 06 '18 at 06:26
  • Interestingly every example given here https://learn.microsoft.com/en-us/sql/t-sql/statements/delete-transact-sql is terminated even though the terminator is supposedly optional ([]). – P.Salmon Apr 06 '18 at 06:38
  • 1
    I can't repro this on any version of SSMS. Perhaps there are non-displayable characters in your first script. Do you get the error if you type the delete script from scratch into a new query window? – Dan Guzman Apr 06 '18 at 10:23
  • 1
    Dan Guzman is probably right. I've seen this before. Usually it's because of carriage return/line feed characters hanging out after a copy and paste. It's insanely irritating. Start with a new, clean tab and type, don't paste, your query. – Brian Stork May 07 '18 at 14:58

2 Answers2

0

I would be curious to know if this was the only script in the batch? I suspect a character somewhere prior to the code you're running is causing the problem.

There are two ways to validate this:

  1. Open a new tab in SSMS, enter delete from foo go and execute.
  2. Add a terminator prior to the statement ;delete from foo go.

In those circumstances does the error still occur?

pim
  • 12,019
  • 6
  • 66
  • 69
0

Take the GO out. It's not a T-SQL statement, it's a management studio batch breaker.

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.

Check this Link SQL Server Utilities Statements - GO for more details.

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 to DELETE table better to use WHERE Condition, the WHERE clause specifies which record(s) should be deleted. If you omit the WHERE clause, all records in the table will be deleted.

Basic DELETE Syntax:

DELETE FROM table_name
WHERE condition;
CR241
  • 2,293
  • 1
  • 12
  • 30