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

muaddba

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?

Chris Auckland

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?

muaddba

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"

atompkins

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.

Chris Auckland

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.