SSMS stopped loading my stored procedures after upgrade

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 there just fine. I get an error message:

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

Additional Information:

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

Some old messages ( 1 , 2 , 3 ) Online conversations about this are caused by the presence of nested comments before "ALTER PROCEDURE" (or simply, as a rule, in a procedure somewhere). In my case, none of my scripts open after this last SSMS update. I reviewed one of my simpler scripts on another computer with an older version of SSMS, and the only comment in all of this is a simple auto-generated comment that reads:

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

Does anyone else encounter this problem after updating SSMS 2016-09-20, and who has a solution?

Update 1: Tip for using the Create To function works on opening procedures. I tried modifying one of the simple procedures so that there are absolutely no comments, and I still get a "script" error when trying to change it! I even checked this procedure after sp_helptext , and in reality it does not contain slashes (/) or stars (*).

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

There is a known issue with the release of SSMS 16.4, and we returned the download to SSMS 16.3. We will update the download link when the problem is resolved.

If you installed SSMS 16.4 and want to revert to SSMS 16.3, you must uninstall SSMS 16.4 before installing SSMS 16.3.

Update 3: Fixed bug in version 16.4.1 (13.0.15900.1). For me, both Modify and Alter To work again after installing 16.4.1 .

Answer 1

"Script Stored Procedure As> Create For" will work. And you can manually change it to Alter. The jhipp clause also works.

Note:

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

Answer 2

The Microsoft SQL team released an update (version 13.0.15900.1) that fixes this problem: ( Download SQL Server Management Studio (16.4.1) )

Note that the workaround for version 13.0.15800.18 is: Tools -> Options -> SQL Server Object Browser -> Scripting -> Convert Custom Data Types to Base Types = True

Adds " EXECUTE AS CALLER " to stored procedures and scalar functions

After installing version: 13.0.15800.18, even if you uninstall and install an older version ( Previous releases of SQL Server Management Studio ), the problem continues

Answer 3

This is happening to me - it seems like this is a common problem with the latest update.

Someone reported an error for MS that had some kind of activity that you can use here: https://connect.microsoft.com/SQLServer/Feedback/Details/3103831

The current workaround is to upgrade the tools -> Options -> SQL Server Object Explorer -> Scripts -> Converting custom data types to base types = True, however, this can have some unintended consequences.

I am currently using SSMS 2014. I hope they fix it quickly (although I don't cross my fingers).

Answer 4

Microsoft is aware of this issue and has released a public release of a quality version that includes a fix

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

Answer 5

Alternatively, instead of changing the SP from SSMS, try running it as Alter To New query window.

Answer 6

This is a mistake, and I hope that there will be a correction soon. Workaround I'm using: Use the CREATE script parameter and change CREATE to ALTER yourself. What is offered here: https://connect.microsoft.com/SQLServer/Feedback/Details/3103831 .