Snapshot vs Database Comparison and Extended Properties

Compares and synchronizes SQL Server databases, backups and scripts.

Moderators: JonathanWatts, Chris Auckland, David Atkinson, eddie davis, Anu Deshpande, Michelle Taylor, alice.easey, james.billings, chengvoon.tong

Snapshot vs Database Comparison and Extended Properties

Postby howarthcd » Tue Jan 03, 2012 3:10 pm

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:
Code: Select all
-- 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:

Code: Select all
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
howarthcd
 
Posts: 50
Joined: Wed May 16, 2007 10:30 am

Postby eddie davis » Thu Jan 05, 2012 6:58 pm

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
Technical Support Engineer
Red Gate Software Ltd
E-mail: support@red-gate.com
eddie davis
 
Posts: 942
Joined: Wed Jun 14, 2006 3:47 pm
Location: Red Gate Software

Postby howarthcd » Thu Jan 05, 2012 9:52 pm

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):
Code: Select all
-- 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:
Code: Select all
-- 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
howarthcd
 
Posts: 50
Joined: Wed May 16, 2007 10:30 am

Postby eddie davis » Wed Jan 25, 2012 4:56 pm

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
Technical Support Engineer
Red Gate Software Ltd
E-mail: support@red-gate.com
eddie davis
 
Posts: 942
Joined: Wed Jun 14, 2006 3:47 pm
Location: Red Gate Software


Return to SQL Compare 10

Who is online

Users browsing this forum: No registered users and 0 guests