Azure Managed Database Instance - Can't modify stored procedures

Question

All, our company uses Azure Managed Database Instance and they work fine, except when I try to right-click a procedure and select modify, it produces an error message: Invalid version: 16. (Microsoft.SqlServer.Smo).

This happens to any stored procedure I try to modify. It also happens if I right-click a procedure and try to script it ... But, if I connect to this db server from an on-premise sql server instance, I can modify or script the procedure without any problem. This appears to be an issue with Azure Managed Instance.

I checked the permissions on the user, tried connecting from on-premise sql server, searched for online help. I haven't found any answers to this question.

Answer 1

As @Thom A notes, you probably just need to update SSMS, but alternatively, here's how to modify a stored procedure without SMO/SSMS scripting it for you.

You can use OBJECT_DEFINITION to get the stored procedure DDL, and then print it to the console. Then copy and modify it (change CEREATE to CREATE OR ALTER). eg

declare @sql nvarchar(max) = (
select OBJECT_DEFINITION(object_id('dbo.uspGetBillOfMaterials')) d)

SELECT @sql AS [processing-instruction(x)] FOR XML PATH('')

The PRINT command truncates long strings, so this trick of using xml allows long strings to be sent to the client without character escaping.