SSMS stopped loading my stored procedures after update

Question

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:

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.

Exclamation

use sp_helptext and look for */ in the comments. Read this: sqlservercentral.com/Forums/Topic738639-149-1.aspx – Gardner

@RicardoC: I just tried sp_helptext on several procedures, and many of them have nothing more than some line-based comments starting with "--". These procedures have no slash (/) or star (*) anywhere in them. Some of the comment lines contain equal signs (=) used for aesthetics. – Exclamation

I have the same issue with SSMS 13.00.15800.18 installed yesterday. On another machine with SSMS 13.0.15700.28 it works fine. – Rondo

@Exclamation The text definitions are stored in [master].[sys].[syscomments] I suggest to find the one for your stored procedure, then change the query window to output to text instead of grid in SSMS, then inspect the output in some editor that would show you the CR and LF special characters. It is possible that one of them is out of place. – Gardner

Answer 1

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

Lather

Thanks for the tip. I had already tried Alter To since I used to use that one to avoid a glitch in an old version of SSMS when choosing Modify. Create To lets me at least open the procedures, which although annoying, still gets the job done. – Exclamation

@Exclamation the only difference between these three commands is that the first uses CREATE and doesn't allow you to accidentally modify a stored procedure. Which is why I generally prefer Create To, make whatever changes I need and change CREATE to ALTER only when finished – Croaky

@PanagiotisKanavos: I know what you mean. Talking about danger, older versions of SSMS would execute whatever text was selected upon pressing F5. For example, if you had "DELETE FROM xyz" selected in your procedure and pressed F5, bad things might happen. – Exclamation

Answer 2

Microsoft SQL team released an update (version 13.0.15900.1) that resolves this issue: (Download SQL Server Management Studio (16.4.1))

Note that the workaround for version 13.0.15800.18: Tools -> Options -> SQL Server Object Explorer -> Scripting -> Convert user-defined data types to base types = True

Is adding "EXECUTE AS CALLER" to stored procedures and scalar functions

Once you install version: 13.0.15800.18 even if you uninstall and install an older version (Previous SQL Server Management Studio Releases) the problem continues

Amadus

Answer 3

It's happening to me as well -- it looks to be a common issue with the latest update.

Somebody logged a bug for MS which has had some activity that you can follow here: https://connect.microsoft.com/SQLServer/Feedback/Details/3103831

A current workaround is to update Tools -> Options -> SQL Server Object Explorer -> Scripting -> Convert user-defined data types to base types = True, however this might have some unintended consequences.

I'm currently using SSMS 2014 in the meantime. Hopefully they get a fix out quickly (though I'm not crossing my fingers).

Hairstyle

Thanks for the find on the bug report. This finding lets me know it's not just me, and gives my post more credence. – Exclamation

Microsoft has rescinded the update, as I have now included in my question. – Exclamation

Answer 4

Microsoft is aware of the issue and released a quality version public release includes the fix

https://connect.microsoft.com/SQLServer/feedback/details/3103831

Sym

Answer 5

Also, instead of Modifying the SP from SSMS, try scripting it as Alter To New Query Window.

Aitch

Alter To gets (basically) the same error as Modify, but Create To works as suggested by lhsoftware. – Exclamation

Good to know. Thank you! – Aitch

Answer 6

It's a bug and I hope there will be a fix soon. The workaround I use is: Use the CREATE script option and change the CREATE to ALTER by yourself. Which is suggested here: https://connect.microsoft.com/SQLServer/Feedback/Details/3103831.

Needham

Answer 7

There are several ways to get the text of the stored procedure back. The only one that I found that didn't have a limit on the number of characters returned is the syscomments table.

You can recover the full stored procedure with this command. It will return an array of 4000 character elements from the stored procedure. Each array element will contain several lines from the stored procedure - broken up wherever the 4000 character limit hits. Copy all the lines to your SQL editor, then go through the lines in the editor to rejoin lines that were split at the end of each 4000 character set.

select text

from

syscomments c

inner join sys.procedures p on p.object_id = c.id

where

p.name = 'stored procedure name'

When I searched for the error, this is what I found within the comments at the start of my code:

/* USE GBDB

DECLARE @intErrorCode INT, @vchErrorMessage VARCHAR(25), @RunReport INT = 1

IF (@RunReport = 1) BEGIN

   IF (OBJECT_ID('tempdb.dbo.#tblspLocal_CalculateNeedsList') IS NOT NULL)
   BEGIN
          DROP TABLE #tblspLocal_CalculateNeedsList
   END

   CREATE TABLE #tblspLocal_CalculateNeedsList (
          Id                                              INT IDENTITY,
          ReportSection                     INT,
   /*
   Section 1 - Grinds Needed
   */

In this case, it was caused by nesting /* */ comment tags.

I replaced that part of the comment with:

   --Section 1 - Grinds Needed

And the error disappeared.

Bust