Search code examples
sql-servervisual-studiosql-manager

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..

Solution

  • 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.