Blog
Aug 16

Resolve BACPAC Export Exception: MS_Description is Not Supported

Recently ran into this interesting issue when trying to export a BACPAC:

01-failedexport

02-MS_DescriptionNotSupported

The MS_Description extended property is apparently not supported by the export data-tier application utility.

So how do we find the offenders? Here are two SQL scripts to find descriptions on tables and columns:

DECLARE @ExtendedPropertyName VARCHAR = 'MS_Description'

-- Find Tables with Descriptions
SELECT              OBJECT_NAME(ep.major_id) AS TableName,
                    ep.[Value] AS [Description],
                    'EXEC sp_dropextendedproperty @name = ''' + @ExtendedPropertyName + ''', @level0type = ''schema'', '
                    + '@level0name = ' + OBJECT_SCHEMA_NAME(ep.major_id)
                    + ', @level1type = ''table'', @level1name = ' + OBJECT_NAME(ep.major_id)
                    AS DropTableDescriptions
FROM                sys.extended_properties ep
WHERE               ep.class_desc = 'OBJECT_OR_COLUMN'
                    AND ep.minor_id = 0
                    AND ep.NAME = @ExtendedPropertyName
  
-- Find Columns with Descriptions
SELECT              OBJECT_NAME(ep.major_id) AS TableName,
                    columns.NAME AS ColumnName,
                    ep.[Value] AS [Description],
                    'EXEC sp_dropextendedproperty @name = ''' + @ExtendedPropertyName + ''', @level0type = ''schema'', '
                    + '@level0name = ' + OBJECT_SCHEMA_NAME(ep.major_id)
                    + ', @level1type = ''table'', @level1name = ' + OBJECT_NAME(ep.major_id)
                    + ', @level2type = ''column'', @level2name = ' + columns.NAME
                    AS DropColumnDescriptions
FROM                sys.extended_properties ep
INNER JOIN          sys.columns ON columns.object_id = ep.major_id
                    AND columns.column_id = ep.minor_id
WHERE               ep.class_desc = 'OBJECT_OR_COLUMN'
                    AND ep.minor_id > 0
                    AND ep.NAME = @ExtendedPropertyName

It should display something similar to this, assuming there are MS_Description properties on any of your tables or columns:

03-SQLResults

To remove the properties, copy the last column in the results from the above query, paste them in your editor, and execute them. An example generated script:

EXEC sp_dropextendedproperty @name = 'MS_Description', @level0type = 'schema', @level0name = dbo, @level1type = 'table', @level1name = 
EXEC sp_dropextendedproperty @name = 'MS_Description', @level0type = 'schema', @level0name = dbo, @level1type = 'table', @level1name = {{YOUR_TABLE_NAME}}, @level2type = 'column', @level2name = {{YOUR_COLUMN_NAME}}

Once you have those cleaned up, you should be able to do another export:

04-success

Done!

SQL based off code from this thread: Drop extended property “MS_Description” of ALL tables and ALL columns