16

Executing the following SQL:

drop function f
go

in MS Sql Server Management Studio give me this parse error:

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'go'.

Why?

If I open a new tab and copy/paste the SQL into it, it also fails. But If I open a new tab and retype the SQL entirely, it works fine.

Eldritch Conundrum
  • 8,452
  • 6
  • 42
  • 50
  • 3
    You presumably have a non visible character in there that messes things up. – Martin Smith Oct 12 '11 at 09:57
  • Clearly whatever you were copy-pasting isn't correct - why not copy-paste what you _typed in_, then you know it'll work? – Widor Oct 12 '11 at 09:58
  • possible duplicate of [What could cause SSMS to not parse SQL properly?](http://stackoverflow.com/questions/7423084/what-could-cause-ssms-to-not-parse-sql-properly) – Martin Smith Oct 12 '11 at 09:59
  • 1
    yes. I ended up findind the solution myself, but I decided to post it here as I was frustrated that I couldn't find it with a web search. But it will probably remain hard to find, since the problem could happen in different kinds of SQL code. Some queries do work with CR though, I only had the problem on DROP FUNCTION. – Eldritch Conundrum Oct 12 '11 at 10:15
  • @EldritchConundrum - Yes actually a different issue from the one that I thought it would be linked above. – Martin Smith Oct 12 '11 at 10:28
  • Well, it is similar. Only, here the cause is a parser bug, not a bad input. – Eldritch Conundrum Oct 12 '11 at 10:41

5 Answers5

35

SQL Server Management Studio can't handle some non printable characters.

Check the newline characters, probably you have Linux (LF) or Mac style (CR) instead of Windows style (CR and LF). You can check with any advanced text editor, for example Notepad++·

DavidEG
  • 5,857
  • 3
  • 29
  • 44
  • The UTF-8 character was never visible using the above techniques. SSMS was showing me with a very tiny red underline where the problems were. I was able to replace this unknown character with an empty string (no value) using Shift-Arrow > Ctrl-C > Ctrl-H > Ctrl-V. The SQL script then ran correctly. – gregsonian Nov 02 '21 at 15:21
6

You opened a file in Mac format, with Carriage Returns ('\r') newlines.

The SQL parser behaves inconsistently on CR newlines. It supports them for some queries, like "select 1 go", but fails on others, like "drop function f go".

Convert all your sql files to windows encoding.

Eldritch Conundrum
  • 8,452
  • 6
  • 42
  • 50
  • 1
    The SQL (actually, management studio) parser is consistent - in neither case does it interpret the gap before the `go` as a newline. It's just that, for your `select 1 go` example, `go` is a perfectly valid column alias, and the `as` keyword is not required. – Damien_The_Unbeliever Oct 12 '11 at 10:47
  • I now understand why "select 1 go" work. Since "select 1 from foo f" works and "select 1 from foo f go" fails, the syntax parsing issue is probably specific to the use of GO and the newline after it. – Eldritch Conundrum Oct 12 '11 at 12:10
2

You should remove all the "GO" from the script and it will resolve the issue.

Check this out for more info:

https://agilewebhosting.com/knowledgebase/63/SQL-Error-Incorrect-syntax-near-andsharp039GOandsharp039.html

Matrix

Matrix Gansburg
  • 51
  • 2
  • This answer may be useful for .NET users running sql scripts with the SqlCommand class. See: http://stackoverflow.com/questions/18596876/go-statements-blowing-up-sql-execution-in-net – andrew pate Mar 16 '17 at 11:20
  • for me after GO removing semi-colun(;) works – Mohd Qasim Aug 02 '23 at 06:14
1

Been suffering with this problem mightily. Finally, used Notepad++.

Fixed by:

Format>Convert to UNIX

followed by

Format>Convert to Windows

sympatric greg
  • 2,969
  • 2
  • 24
  • 29
0

I am too facing the same issue, but not sure about what causes it. When copy paste the in query editor, able to execute it.

Thorough procedure only it failing

Scripts generated from my procedure.

Msg 102, Level 15, State 1, Line 366 Incorrect syntax near 'GO'. Msg 156, Level 15, State 1, Line 370 Incorrect syntax near the keyword 'CREATE'. Msg 102, Level 15, State 1, Line 371 Incorrect syntax near 'GO'.

CREATE TABLE dbo.[SolutionAssessmentBPF] ( businessprocessflowinstanceid uniqueidentifier NOT NULL , StatusReasonCode int NULL , TimeZoneRuleVersionNumber int NULL , TraversedPath nvarchar (1250) NULL , UTCConversionTimeZoneCode int NULL , TenantId int NOT NULL , OdsCreatedDate datetime NOT NULL DEFAULT GETUTCDATE() , OdsModifiedDate datetime NOT NULL DEFAULT GETUTCDATE() , OdsStatus tinyint NULL DEFAULT 0 , VersionNumber bigint DEFAULT -1 , CONSTRAINT [PK_SolutionAssessmentBPF] PRIMARY KEY CLUSTERED ( businessprocessflowinstanceid,TenantId ASC )) GO

CREATE UNIQUE INDEX [UQX_SolutionAssessmentBPF_bpf_incidentid_OdsStatus] ON [dbo].[SolutionAssessmentBPF] ([bpf_incidentid], [OdsStatus]) GO CREATE NONCLUSTERED INDEX [NCI_SolutionAssessmentBPF_OdsModifiedDate] ON [dbo].[SolutionAssessmentBPF] ([OdsModifiedDate]) GO

This issue is got fixed after removing GO statement from dynamic SQL generated. Please remove GO statement and any non printable character which is not compatible with windows format. That should fix this kind of issues.

yuvraj
  • 181
  • 1
  • 12