Snapshot vs Database Comparison and Extended Properties

howarthcdhowarthcd Posts: 70 Bronze 3
edited January 25, 2012 10:56AM in SQL Compare Previous Versions
I'm experiencing an issue when comparing a snapshot vs the database that the snapshot was generated from whereby a difference is indicated in the main window but where there are no differences highlighted in the comparison pane when the object is selected - indeed the 'Next' button is greyed-out yet the synchronisation script contains a statement to update an extended property.

SQL Compare 10.0.0.160

To reproduce the issue you should perform the following steps:

1. Create a new database named 'Test' on a SQL Server instance (I used 2008 R2).
2. Execute the following script: 
-- Columns
CREATE TABLE [dbo].[Test]
(
[TestID] [int] NOT NULL IDENTITY(1, 1)
)
GO
-- Extended Properties
EXEC sp_addextendedproperty N'MS_OrderBy', NULL, 'USER', N'dbo', 'TABLE', N'Test', NULL, NULL
GO
3. Use SQL Compare to create a snapshot of the database.
4. Perform a comparison using the snapshot as the source and the Test database as the target.
5. Examine the differences.

I found that the synchronisation script contained the following statement:
EXEC sp_updateextendedproperty N'MS_OrderBy', NULL, 'SCHEMA', N'dbo', 'TABLE', N'Test', NULL, NULL
GO

Note that no difference is indicated if Extended Properties are excluded using the project options.

Also I have found that the 'difference' is not highlighted when the snapshot is compared with itself.

This seems to occur for Extended Properties where the value of the @value parameter (when calling sp_addextendedproperty) is NULL.

Chris
· Share on Twitter

Comments

  • Eddie DEddie D Posts: 1,803 Rose Gold 5
    Hi

    Thank you for your post into the forum.

    Are you comparing a SQL 2000 database or SQL 2005 or higher running in Compatibility Mode 80 with a SQL 2005 or higher database?

    I ask the question, if my memory serves me correctly, USERS in SQL 2000 become SCHEMAS in SQL 2005 and higher, which may explain the behaviour you are seeing.

    Many Thanks
    Eddie
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
    · Share on Twitter
  • howarthcdhowarthcd Posts: 70 Bronze 3
    Hi Eddie

    The problem was originally highlighted when comparing a snapshot generated from a SQL 2000 database with the original SQL 2000 database (this was a test I performed prior to an upcoming schema change). Note that comparing the original database with itself does not result in this issue so the problem seems to be related to snapshot vs database comparisons.

    However the problem is repeatable under SQL 2008 R2 with a database in the 100 compatibility level and a snapshot created from the same database.

    After further testing it is definitely the NULL value being assigned to the extended property that is causing the problem as assigning a value of, say, '123' causes no differences to be identified by SQL Compare.

    Going back to my original example, the following statement causes a table-level difference to be indicated (although there are no differences in the SQL difference pane): 
    -- Extended Properties 
EXEC sp_addextendedproperty N'MS_OrderBy', NULL, 'SCHEMA', N'dbo', 'TABLE', N'Test', NULL, NULL 
GO
    ...the following statement does not cause a table-level difference to be indicated: 
    -- Extended Properties 
EXEC sp_addextendedproperty N'MS_OrderBy', '123', 'SCHEMA', N'dbo', 'TABLE', N'Test', NULL, NULL 
GO

    The conversion from USER to SCHEMA in the synchronisation script is just a distraction. I'm more concerned that SQL Compare is indicating that there is a difference that doesn't actually exist, particularly as the database that I'm trying to work with has around 200 tables that each have at least one extended property with a NULL value assigned (it's a 3rd-party database that I cannot change, unfortunately). I don't really want to have to totally disable the checking of Extended Properties at the project level as the database has so many.

    Thanks
    Chris
    · Share on Twitter
  • Eddie DEddie D Posts: 1,803 Rose Gold 5
    Hi Chris

    Thank you for your patience.

    I have been able to replicate your fault symptoms and believe this to be a bug. Therefore I have submitted a Bug report for the development team to consider, the reference for this bug report is SC-5567.

    I will provide an update for you, once I receive further information from the development team.

    Sorry that you have encountered this problem and thank you for bringing it to our attention.

    Many Thanks
    Eddie
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
    · Share on Twitter
Sign In or Register to comment.