Issues with Comparing Static Data in SSC 3.0.5.7 (and prior)

A SQL Server Management Studio add-in to source control your database in Subversion or Team Foundation Server.

Moderators: Chris Auckland, David Atkinson, sherr, PhilScrace, andy.campbell.smith

Issues with Comparing Static Data in SSC 3.0.5.7 (and prior)

Postby muaddba » Tue Mar 13, 2012 8:52 pm

I have a table that seems to be throwing SSC for a loop. No matter how many times I sync it with source control, it still shows up with 1 row to commit when I run a comparison.

Using SQL Data Compare, I am able to set the option "force binary collation" to get this discrepancy to go away, however with SSC I cannot get this configuration option, even with the newly created configuration file detailed in another post....only SQL Compare options are offered, not SQL Data Compare options.

How do I resolve this?
muaddba
 
Posts: 12
Joined: Fri Feb 10, 2012 11:12 pm

Postby Chris Auckland » Fri Mar 16, 2012 10:46 am

Thanks for your post.

I'm not sure what the plans are for adding configurable SDC options in SQL Source Control, but I'll check.

What was the discrepancy that SQL Data Compare highlighted?

Could it be caused by using a case sensitive collation, or having a different collation between the table script in source control, and the live database?
Chris
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.

Postby muaddba » Fri Mar 16, 2012 8:29 pm

Hi chris,

This was a brand new repository, created by first committing the changes from the existing database...

The discrepancy is a single row that exists both in the DB as well as in the repository. Here is the full statement:

INSERT INTO [dbo].[FILE_TYPE] ([FILE_TYPEID], [FILETYPE], [DESCRIPTION], [ICONIMAGE], [VIEWABLE], [EXTENSION]) VALUES (N'x-zip-compressed', 'ZIP Document', NULL, 'Zip.gif', NULL, N'.zip')

This is repeated on both sides of the comparison, but one one side it says "Only in Database" and the other side it says "Only in Source Control"
muaddba
 
Posts: 12
Joined: Fri Feb 10, 2012 11:12 pm

Postby atompkins » Mon Mar 19, 2012 10:45 am

I've seen the same thing. I had a couple of tables with nvarchar primary keys. I have worked around it by putting in an int primary and having the original key as a unique but that's not always going to be possible. It seemed a bit like when you use CHECKSUM for a dimension, it ignores certain characters. I'd be interested to know why a binary comparison isn't the default.
atompkins
 
Posts: 13
Joined: Tue Jul 05, 2011 10:45 am

Postby Chris Auckland » Thu Mar 22, 2012 2:35 pm

Sorry for the delay.

The good news is that we're going to add SQL Data Compare options to SQL Source Control in a future version, but I'm not sure exactly when that will be.

The reason that a binary comparison isn't the default is mainly because it will force a case sensitive comparison, which isn't ideal if you're using a case insensitive collation. It will also mean that the collation rules are also ignored.

However, a binary comparison does work better with non-unique indexes.
Chris
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.


Return to SQL Source Control 3

Who is online

Users browsing this forum: No registered users and 1 guest