Red Gate forums :: View topic - Snapshot vs Database Comparison and Extended Properties
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Compare 10
SQL Compare 10 forum

Snapshot vs Database Comparison and Extended Properties

Search in SQL Compare 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
howarthcd



Joined: 16 May 2007
Posts: 50

PostPosted: Tue Jan 03, 2012 3:10 pm    Post subject: Snapshot vs Database Comparison and Extended Properties Reply with quote

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:
-- 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:
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
Back to top
View user's profile Send private message
eddie davis



Joined: 14 Jun 2006
Posts: 921
Location: Red Gate Software

PostPosted: Thu Jan 05, 2012 6:58 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
howarthcd



Joined: 16 May 2007
Posts: 50

PostPosted: Thu Jan 05, 2012 9:52 pm    Post subject: Reply with quote

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:
-- 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:
-- 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
Back to top
View user's profile Send private message
eddie davis



Joined: 14 Jun 2006
Posts: 921
Location: Red Gate Software

PostPosted: Wed Jan 25, 2012 4:56 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group