sql-serverstored-proceduresssms-16

SSMS stopped loading my stored procedures after update


Today I installed version 13.0.15800.18 of Microsoft SQL Server Management Studio (SSMS) [2016-09-20, 16.4], and now it refuses to edit my stored procedures. I even tried a slightly older version on my other computer to make sure, and they open just fine there. I get the error:

enter image description here

Script failed for StoredProcedure 'xyz'. (Microsoft.SqlServer.Smo)

Additional information:

Syntax error in TextHeader of StoredProcedure 'xyz'. (Microsoft.SqlServer.Smo)

Some old posts (1, 2, 3) Online talk about this being caused by having nested comments before "ALTER PROCEDURE" (or just generally in the procedure somewhere). In my case, none of my scripts open after this most recent SSMS update. I viewed one of my simpler scripts on another computer with an older version of SSMS, and the only comment in the whole thing is a simple auto-generated comment that reads:

/****** Object: StoredProcedure [dbo].[xyz] Script Date: 9/21/2016 12:55:48 PM ******/

Has anyone else run into this problem after the 2016-09-20 update of SSMS, and has anyone got a solution?

Update 1: lhsoftware's tip (below) of using Create To works for opening the procedures. I tried modifying one of the simple procedures to have absolutely no comments at all, and I still get the "script failed" error when trying to Modify it! I even checked this procedure afterward with sp_helptext, and indeed it contains no slashes (/) or stars (*) whatsoever.

Update 2: Microsoft has officially rescinded the update, with the following message (found here):

There is a known issue with the SSMS 16.4 release and we have rolled back the download to SSMS 16.3. We will update the download link when the issue has been resolved.

If you have installed SSMS 16.4 and would like to revert to SSMS 16.3, you must uninstall SSMS 16.4 prior to installing SSMS 16.3.

Update 3: The bug has been resolved in version 16.4.1 (13.0.15900.1). For me, both Modify and Alter To work again since installing 16.4.1.


Solution

  • "Script Stored Procedure As > Create To" will work. And you can manually change it to Alter. jhipp's suggestion works as well.

    Note:

    "Script Stored Procedure As > Alter To" will return the same error as "Modify"