Schema compare does not manage MS_Description property for unique keys

Question

Handling of the MS_Description extended attribute on unique keys doesn't appear to be working.

Try this: 

1) Create a unique key on a table using the SSMS table designer and fill-in the description value.

2) Use schema compare to bring the table structure into the project.

The MS_Description extended attribute is not brought in on the table.  A new file is added to the project called "Extended Properties.sql" and it contains entries for the unique keys with incorrect syntax as shown below:

EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Implements a business rule to ensure the value provided for the Row GUID column is not duplicated';

The syntax has the correct attribute and description we provided for the unique key but obviously critical parameters referencing the unique key constraint itself are missing.  We do have our options set to include extended attributes in the schema compare.

If you try to build the project at this point (and you have more than one unique key with an MS_Description attribute), the build will fail because the syntax duplicates the extended property:

Error 2 SQL71508: The model already has an element that has the same name MS_Description. C:\...\Documents\Visual Studio 2010\Projects\SGIFramework\SGIFrameworkDB\ExtendedProperties.sql 5 1 SGIFrameworkDB

FYI - the MS_Description attribute for tables, columns, foreign keys, schemas and even file groups all seem to work fine; just not for unique keys.

AND

3) Then use a schema schema from the project to update the database.

An error results. The schema compare correctly identifies that the project has no MS_Description attribute on the unique key so it sets up a script to drop it from the project.  The script fails however, because the syntax used is incorrect - same problem as the import - parameters are missing.

Example below:

(86,1): SQL72014: .Net SqlClient Data Provider: Msg 15217, Level 16, State 1, Procedure sp_dropextendedproperty, 
Line 28 Property cannot be updated or deleted. Property 'MS_Description' does not exist for 'object specified'.

PRINT N'Dropping [sf].[AccessAudit].[uk_AccessAudit_RowGUID].[MS_Description]...';
GO
EXECUTE sp_dropextendedproperty @name = N'MS_Description';

Answer 1

I tried this, but I couldn't reproduce your bug.  The generated code for me was:

EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Index description.', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Table_1', @level2type = N'CONSTRAINT', @level2name = N'PK_Table_1';

Which is what I would have expected.  I followed your creation steps on a LocalDB install, using SSMS 2012; which type/version of SQL Server are you using?  (I don't know if that would make a difference, but it might help narrow things down.)

Tom

P.S. The code above is for the table primary key; I also tried it with a secondary unique key index, and it produced much the same output.

Answer 2

On viewing your screencast in another question, it seems we are running different versions of SSDT.  According to the Visual Studio 2010 About page, I'm running CTP4 (10.2.11213.0).  Which version are you running?  Could be this is a regression...

Edit: One update to the RTW later, and I still can't reproduce, so don't worry about this.

Answer 3

Kazoo,

Can you please let us know which version you are running with? Help->About Microsoft Visual Studio -> Copy Info

Microsoft SQL Server Data Tools   10.3.20225.0
Microsoft SQL Server Data Tools

If you do not have version 10.3.20225.0 could you try upgrading to the RTM release?

---

-GertD @ www.sqlproj.com

Answer 4

Hi Kazoo,

I'm not able to reproduce this with the RTW product.  The screenshots below from my repro attempt show an SSMS dialog with an extended property on a unique key, and then the SSDT schema compare results when comparing the database against an empty project.  As you can see the full definition of the extended property is reported in the schema compare results and a correctly defined extended property is created in the project after update.  What you are seeing should only occur for extended properties defined at the database level.  Please can you confirm the version of the product you're using?  And could you provide a minimum repro so that we can see if we can track this down?

Answer 5

Thanks everyone, for your help on this. 

1. The SSDT version we are using is:  10.3.20225.0 (full details of VS installed modules below)

2. I'm wondering if the target DB might be making the difference.  We don't use the local db because it does not support full-text search yet which is critical to our applications.  The target DB we are using is SQL Server 2008 R2. 

3. I didn't have time to create a small repro this morning but will get to that in the next day or two.

Thanks again.

Microsoft Visual Studio 2010
Version 10.0.40219.1 SP1Rel
Microsoft .NET Framework
Version 4.0.30319 SP1Rel

Installed Version: Premium

Microsoft Office Developer Tools   01021-532-2002102-70190
Microsoft Office Developer Tools

Microsoft Visual C# 2010   01021-532-2002102-70190
Microsoft Visual C# 2010

Microsoft Visual Studio 2010 Code Analysis Spell Checker   01021-532-2002102-70190
Microsoft Visual Studio 2010 Code Analysis Spell Checker

Portions of International CorrectSpell™ spelling correction system © 1993 by Lernout & Hauspie Speech Products N.V. All rights reserved.

The American Heritage® Dictionary of the English Language, Third Edition Copyright © 1992 Houghton Mifflin Company. Electronic version licensed from Lernout & Hauspie Speech Products N.V. All rights reserved.

Microsoft Visual Studio 2010 Team Explorer   01021-532-2002102-70190
Microsoft Visual Studio 2010 Team Explorer

Microsoft Visual Web Developer 2010   01021-532-2002102-70190
Microsoft Visual Web Developer 2010

Add Reference Dialog Plus!   1.0
An significantly enhanced Add Reference dialog for managed code projects.

AlignAssignments   1.0
Command for aligning assignments.

AutoBraceComplete   1.0
auto brace complete

Crystal Reports Templates for Microsoft Visual Studio 2010  
Crystal Reports Templates for Microsoft Visual Studio 2010

Document Well 2010 Plus   1.0.10916.0
This package provides configurable behavior for the Document Well and its tabs. Please be sure to let us know what you like/dislike about this extension.

Hotfix for Microsoft Visual Studio 2010 Premium - ENU (KB2529927)   KB2529927
This hotfix is for Microsoft Visual Studio 2010 Premium - ENU.
If you later install a more recent service pack, this hotfix will be uninstalled automatically.
For more information, visit http://support.microsoft.com/kb/2529927.

Hotfix for Microsoft Visual Studio 2010 Premium - ENU (KB2548139)   KB2548139
This hotfix is for Microsoft Visual Studio 2010 Premium - ENU.
If you later install a more recent service pack, this hotfix will be uninstalled automatically.
For more information, visit http://support.microsoft.com/kb/2548139.

Hotfix for Microsoft Visual Studio 2010 Premium - ENU (KB2549864)   KB2549864
This hotfix is for Microsoft Visual Studio 2010 Premium - ENU.
If you later install a more recent service pack, this hotfix will be uninstalled automatically.
For more information, visit http://support.microsoft.com/kb/2549864.

Hotfix for Microsoft Visual Studio 2010 Premium - ENU (KB2565057)   KB2565057
This hotfix is for Microsoft Visual Studio 2010 Premium - ENU.
If you later install a more recent service pack, this hotfix will be uninstalled automatically.
For more information, visit http://support.microsoft.com/kb/2565057.

Hotfix for Microsoft Visual Studio 2010 Premium - ENU (KB2581206)   KB2581206
This hotfix is for Microsoft Visual Studio 2010 Premium - ENU.
If you later install a more recent service pack, this hotfix will be uninstalled automatically.
For more information, visit http://support.microsoft.com/kb/2581206.

Hotfix for Microsoft Visual Studio 2010 Premium - ENU (KB2635973)   KB2635973
This hotfix is for Microsoft Visual Studio 2010 Premium - ENU.
If you later install a more recent service pack, this hotfix will be uninstalled automatically.
For more information, visit http://support.microsoft.com/kb/2635973.

Indent Guides   12.1
Indent Guides

Adds visual guides at each indentation level.

Microsoft SQL Server Data Tools   10.3.20225.0
Microsoft SQL Server Data Tools

Microsoft Team Foundation Server 2010 Power Tools   10.0.41206.0
Power Tools that extend the Team Foundation Server integration with Visual Studio.

Microsoft Visual Studio 2010 Premium - ENU Service Pack 1 (KB983509)   KB983509
This service pack is for Microsoft Visual Studio 2010 Premium - ENU.
If you later install a more recent service pack, this service pack will be uninstalled automatically.
For more information, visit http://support.microsoft.com/kb/983509.

Microsoft Visual Studio 2010 SharePoint Developer Tools   10.0.40219
Microsoft Visual Studio 2010 SharePoint Developer Tools

Microsoft Visual Studio Process Editor   1.0
Process Editor for Microsoft Visual Studio Team Foundation Server

Microsoft.VisualStudio.QuickAccess.Package   1.0
Awesome search bar.

OptionsPageImpl   1.0
Information about my package

QuickFind   1.0
Visual Studio 2010 Quick Incremental Find Extension

Security Update for Microsoft Visual Studio 2010 Premium - ENU (KB2645410)   KB2645410
This security update is for Microsoft Visual Studio 2010 Premium - ENU.
If you later install a more recent service pack, this security update will be uninstalled automatically.
For more information, visit http://support.microsoft.com/kb/2645410.

Answer 6

I just tried again with an SQL Server 2008 R2 install, and I still can't reproduce the bug.  However, in re-reading your original question, I realised: I don't get a file called "Extended Properties.sql" at all -- all of my `EXEC sp_extendedproperty` statements are contained within the file for my test table.  Did you say you were getting this from Schema Compare?  Or is it from some other process (e.g. database import)?

Tom

Edit: Well I just ruled out the "import" scenario: that works fine for me.  This one's a stumper!

Answer 7

Tom,

Thanks for trying to repro!  The Extended Properties.sql file is created to hold database-level extended properties, which Kazoo's rogue ones are because they are not associated with objects within the schema.

Cheers, Bill

Answer 8

I've tried a couple more different ways and still not managed to repro.  Did you make any headway with a minimal case where it doesn't work?  A couple more things I thought of that may (or may not!) help track it down:

1) Could you find one of the tables that exhibits this problem in SSMS and "Script Table As CREATE"?  That might reveal a problem then and there, but if not, could you post it here?  That way we can import it and see if it causes the same issue.

2) This probably won't give any useful information, but just in case, could you post the results of

SELECT * FROM sys.extended_properties WHERE value = 'Implements a business rule to ensure the value provided for the Row GUID column is not duplicated';

(or one of the other ones that aren't working).

Thanks,
Tom

Answer 9

Thanks guys for investigating this.

Tom - both suggestions were good ones.  If a table exhibiting the problem is scripted out and created as a new table name, the properties import correctly under the new table name. 

I have included a link to a screen cast below showing results of further analysis: http://screencast.com/t/8eS9iBWRGbyK  

At this point it seems like the "roque" properties might be orphans or possibly duplicates.  I need to analyze what the "correct" dictionary prescence is of extended properties on UK's where they are working correctly.  As per the video "City2" is an example of that and I will check that next and post back.  I am also wondering whether the deployment of these properties from SSDT eventually corrupts them somehow

(I'm just guessing there aren't a lot of organizations as anal as we are about documenting every single object that can be documented in the db so maybe this hasn't come up before :-)

Thanks

Answer 10

Hi,

I've taken a look a the screencast which was useful but as you know doesn't pinpoint the cause.  Here's a couple of things that might shed some light: 

1. Create a database project using Import and see if that creates the same duplicate content.
2. Get a count of all extended properties in the database, then with either a project created by schema compare or import above from which you have first deleted any duplicate extended properties in the ExtendedProperties.sql file, create a new database and then again count the extended properties.  If counts differ by the number of duplicates you deleted then it would suggest that the 'rogues' are really present in the database and need to be identified and removed (and the database monitored to see if they return).  If the counts are the same it suggests that something about schema compare and/or import is faulty and needs to be investigated.     

If you conclude there are rogue properties in the database, then comparing the project to the database after the exercise in 2. should highlight the properties as Deletes - be sure to enable Database options, permissions and extended properties in schema compare object type options under non-application scoped.  If you convince yourself they are bad then you could just apply the deletes (and again monitor the situation in case they come back).  We have no evidence at this point that suggests SSDT is corrupting the database but it can't be ruled out either.

Cheers,
Bill

Answer 11

Thanks Bill for your suggestions.  I went through the steps outlined.  I am reasonably confident  there are extra properties in the database, however the schema compare doesn't detect them as duplicates through the method above.  

I believe they are duplicates however, because for tables which have been scripted out, renamed and schema compared back into the project; they don't exhibit the duplicates.  The extended property for those objects only appears on the "constraint" object type and not the "index" object type.

So, I wrote a script to delete the duplicate extended properties (the property on "index" objects), but when I used schema compare to pick up the changes and apply them, this also had the effect of removing the descriptions from the "constraint" objects. Confusing.

There is a compounding problem (bug I think) that is complicating things.  If you create an extended property on an object, but the object has no structural change - neither the  schema compare nor publish operation will detect the change and apply it.  This is making it difficult to figure out what SSDT is doing when it is pushing extended properties into the database because you can't do that independently of structure changes.

To repro this issue:

• create a table in SSMS with at least 2 columns
  
• in SSMS add a description to 1 of the  columns
• create a new project
• in SSDT schema compare the table  into your project
• in the project - add syntax to create a description on the other column
• schema compare to the DB - the tool doesn't mark the  additional property as a change for deployment

I'll try to come up with a simpler test method that can show what is happening to properties being published from SSDT.  At this point, I'm still not sure where the duplicates are coming from.

Thanks  

Answer 12

WRT the schema compare problem... I hate to say it, but it works perfectly for me.  I followed your steps: created a table in SSMS (on SQL Server 2008 R2), added a description on a column, and SC'd it into my SSDT project.  I made the change locally, did the reverse SC, and it shows up fine as a change.

The only way I could make it ignore those changes was to go to the Schema Compare options and uncheck 'Extended Properties' under Object Types/Application-scoped.  Could you see if that's set correctly on your SC?  That would at least solve the one mystery!

Tom

Answer 13

OK - final installment.

When the properties were originally added to the unique keys, there were added to the "index" object.  This was done by us - not SSDT.  When the extended property for a unique key is placed on the index, the behavior reported at the top of this thread occurs (rogue extended attributes from schema compare).   In the SQL dictionary views, when you add an extended property to the index enforcing the unique key, the extended property appears both under the "index" object and the constraint object.  So - our bad:  don't assign descriptive properties to the index of unique keys - just attach them to the constraint and don't touch the index.  When this is done, you only see the property once in the sys views and  behaves as it should.  The root problem was our mistake in how the extended attribute was assigned.

Once the error was identified, we dropped the  extended attributes from the (unique key) index objects in the database and recreated them on the constraint object.  The database views returned results without duplicates.  We used  schema compare to bring those corrections back into the SSDT projects, however, something must have been corrupted in the database project.  We could not avoid continuing to get some rogue properties and we started seeing other behavior that could not be reproduced in new projects (as Tom pointed out).

In the end, we created a new project from the database and that resolved all  extended property issues.  Today we have added and removed dozens of these properties and SSDT continues to manage them reliably.

Thanks again guys for your help on this issue.  I'm sorry it took so much time to end up with  "user error" as the basis of the problem.  

Answer 14

No worries -- just glad that you've got it sorted!

Tom

Answer 15

Hi,

No problem, always happy to help.  I'm glad you were able to get to the bottom of it.  We'll look into the root cause and see if there was anything we could have done better here.

Cheers,

Bill