CODEWITHSUNDEEP
sql-serverstored-proceduresssms
Gary Chapman
Gary Chapman

Reputation: 418

SSMS removing pre-BEGIN comments from my stored procedures

I'm running SSMS 12.0.2000.8

If I use the SSMS query editor to create a stored procedure (such as the one below) the comments before BEGIN are removed when I execute/save it:

CREATE PROCEDURE myproc
/* Say goodbye to this comment */
    @var1 int -- this comment will disappear too
AS
BEGIN
   /* This comment is safe */
   select 'hello' -- this too shall endure
END

A colleague is running the same version of SSMS and has no such problems. If I execute one of his scripts using sqlcmd.exe the comments get stripped then too. I presume there must be a global setting that I need to change but I have no idea where it might be.

Upvotes: 2

Views: 3571

Answers (4)

Forch
Forch

Reputation: 11

While the specified solutions didn't work for me, it certainly pointed me in the right direction.

For me, the behavior was only occurring on a single SQL Server connection, and it was because "Always Encrypted" was set to "enable". When I disabled it, my comments remained in the stored procedure code.

Screen Shot of SSMS 18.4

Upvotes: 1

Gary Chapman
Gary Chapman

Reputation: 418

After observing some other strangeness (namely with execute as caller being added to my scripts), I did some Googling and discovered the answer:

delete \Users\[user]\AppData\Roaming\Microsoft\SQL Server Management Studio\12.0\sqlstudio.bin

WARNING: You will lose your current list of memorized SQL Servers/usernames/passwords.

Upvotes: 3

nil_jones_pdx
nil_jones_pdx

Reputation: 41

I was experiencing the same issue and found that it was caused by Tools -> Options -> SQL Server Object Explorer -> Scripting -> Convert user-defined data types to base types.

When this is "True" I lose my comment blocks. When "False" my comment blocks came back.

Upvotes: 4

James Z
James Z

Reputation: 12318

I tested this with SQL Server 2008 and SSMS 12.0.2000.8, and this was the result after "Script stored procedure as" - > "Create to" -> "Clipboard":

CREATE PROCEDURE [dbo].[myproc]
/* Say goodbye to this comment */
    @var1 int -- this comment will disappear too
AS
BEGIN
   /* This comment is safe */
   select 'hello' -- this too shall endure
END
GO

Have you tried checking with sp_helptext if the comments are in the procedure before you use the scripting tool?

I also checked the options, can't find anything there related to comments, or stripping away anything like that.

Upvotes: 2

Related Questions