Object reference not set to an instance of an object. (SqlMgmt) while updating extended properties with SSMS wizard

Question

While updating (Click on OK button, or Script on SSMS Wizard page), SSMS got Error: Object reference not set to an instance of an object. (SqlMgmt)

Although there is no problem to do that action with system procedure (EXEC sys.sp_updateextendedproperty) and this will be execute successfully, but it seems that SSMS can't generate this action on wizard page (Stored Procedure Properties).

• I had reinstall SSMS on my local system, but it didn't worked!

• Same Error exists on every local system of our client and developer that connect to this instance! and I believe that this is a problem on instance wide of server, not SSMS on client side.

• There is no Problem for update extended properties from other Object (like Tables, Views, function, etc), but just Stored Procedure!

• I think maybe there is some dirty records in some System Tables on the msdb Database!!!

Answer 1

I solve it myself! The Problem was that I had an Extended Property with a NULL value on all my Stored Procedures!!!

So to solve this problem should update all of them and generate the Not NULL value for them.

Below sample code will be update all the values of entire stored procedures that are exists with the NULL values in their extended properties (Just replace the YOUR_EXTENDED_PROPERTY_NAME with your own extended property name that have NULL value and should be update):

DECLARE @Query AS NVARCHAR (MAX);
DECLARE c CURSOR FOR
----------------------------------------------------
SELECT 'begin try EXEC sys.sp_updateextendedproperty @name=''YOUR_EXTENDED_PROPERTY_NAME'', @value='''' , @level0type=''SCHEMA'',@level0name='''
       + schemaName + ''', @level1type=N''PROCEDURE'',@level1name=N''' + spname + ''' End try Begin catch End catch'
FROM (
         SELECT Ob.name  spname,
                EX.name  extended_name,
                sch.name AS schemaName,
                EX.value
         FROM sys.extended_properties AS EX
         JOIN sys.objects             AS Ob ON Ob.object_id = EX.major_id
         JOIN sys.schemas             AS sch ON sch.schema_id = Ob.schema_id
         WHERE Ob.type = 'P'
               AND EX.value IS NULL
     ) t;

----------------------------------------------------
OPEN c;
FETCH NEXT FROM c
INTO @Query;
WHILE @@fetch_status <> -1
BEGIN
    EXECUTE sp_executesql @Query;
    FETCH NEXT FROM c
    INTO @Query;
END;
CLOSE c;
DEALLOCATE c;

Question is that, why Microsoft let to insert the NULL value and then got ERROR Object reference not set to an instance of an object. on them!!!