CODEWITHSUNDEEP
sql-servervisual-studiosql-manager
elle0087
elle0087

Reputation: 911

incorrect syntax near 'go' - can t have ' into double comment

I don't understand what's happening.

This generates an error

create procedure sp_test
as
/*
   /*
      a
   */
   e'
*/
begin
print''
end
go

"Msg 102, Level 15, State 1, Procedure sp_test, Line 13 Incorrect syntax near 'go'."

While this works

create procedure sp_test
as
/*
   /*
      a
   */
   e
*/
begin
print''
end
go

Why if I have two nested comments in the main comment I can't have ' symbol? I discovered this bug using VS Sql compare to generate the db script and is not possible to have any other GO after this.

Instead using Sql Management it will generate the single sp_test script without GO..

Upvotes: 2

Views: 1666

Answers (2)

Solomon Rutzky
Solomon Rutzky

Reputation: 48914

While SQL Server itself allows for nested block comments, the batch parsing code used by SSMS, SQLCMD.EXE, and possibly SMO, has a bug in it that doesn't handle these nested block comments entirely. I emphasize "entirely" because that code will handle them to a degree. It all depends on how they are nested and where the embedded GO or apostrophe, etc are. Some combinations work while others do not. For example, the following adaptation of your non-working test does work because I added a 2nd nested comment:

create procedure sp_test
as
/*
   /*
      a
   */
   /*
   e'
   */
*/
begin
print''
end
go

I posted this bug to Microsoft Connect back in March, but I doubt it will ever be seen as having enough priority to devote developers to, sadly:

"GO" in 2nd half of nested block comments breaks batch parsing in SSMS and SQLCMD

P.S. It should be noted that the older OSQL.EXE does not seem to have this particular parsing problem, though I still would not recommend using it ;-).

Upvotes: 0

Lasse V. Karlsen
Lasse V. Karlsen

Reputation: 391664

This must be a bug in SQL Server Management Studio.

The GO statement is not a real statement that SQL Server knows how to handle but a convention that editors, such as Management Studio and the command line client, uses to delimit big queries into smaller pieces.

These smaller pieces are then executed one by one in order.

As such, if the GO command is actually sent to SQL Server to execute, it won't know how to handle it and thus gives you the error you got.

In Management Studio 2014, the syntax coloring is fine with the nested comments, but the presence of the apostrophe inside trips up the code that tries to delimit the query into smaller pieces.

As such I think the bug here is that the code that tries to split on the GO statement does not in fact support nested comments and thus is tripped up by the presence of them. Basically it seems to think that the comment ends after the inner */, which is wrong, and then the apostrophe is considered the start of a string that has no end that then encapsulates everything that follows, including the GO command.

Thus everything after the apostrophe is sent to SQL Server. SQL Server does support nested comments so it will see the GO command as a statement, which it doesn't support, and thus the error.

I have reported this using Microsoft Connect here: SQL Server 2014 Management Studio, when delimiting on GO command, doesn't handle nested comments.

Upvotes: 1

Related Questions