Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 856
Michael Avatar asked Sep 21 '16 18:09

Michael


People also ask

What is wrong with stored procedures?

Stored procedures introduce a cliff (or disconnect) between coherent functionality, because the domain logic gets split between the application- and the database layer. It's rarely clear where the line is drawn (e.g. which part of a query should go into the application layer and which part into the database layer?).

How do I repair Microsoft SQL Server Management Studio?

Launch the SQL Server Setup program (setup.exe) from SQL Server installation media. After prerequisites and system verification, the Setup program will display the SQL Server Installation Center page. Click Maintenance in the left-hand navigation area, and then click Repair to start the repair operation.


1 Answers

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

like image 87
AdroitOldMan Avatar answered Oct 11 '22 21:10

AdroitOldMan