Incorrect differences shown for data changes
I just did a data edit (inserted one row) but in the differences I see three rows inserted and two removed. Two of the rows inserted are letter for letter identical to the two rows removed.
Doesn't really matter, just a little confusing to read
I have the same problem, but I could divide it on two topics:
First is connected with option "Force binary collation", if I switch off this option then comparison result is correct (although the data are identical)
Second is connected with some characters: #, $, + (these values are primary key values).
In such case a conflict is shown). Both comparison sites contain following statements:
INSERT INTO <table1> (<columns>) VALUES (N'#', N'Hash', NULL)
INSERT INTO <table1> (<columns>) VALUES (N'$', N'Dolar', NULL)
INSERT INTO <table1> (<columns>) VALUES (N'+', N'Plus', NULL)
(Database collation: SQL_Latin1_General_CP1_CI_AS)
This is not only problem of SQL Source Control, our build server uses SQL Comparer and SQL Data Comparer command line tools to build nightly our development server and it failed when I switch on the option "Force binary collation".
Rob Pearse commented
I have a similar problem, a table showing 7 rows to be removed and 7 identical rows to be added. It looks like its due to the primary key being nvarchar and the data being case sensitive. e.g.
One of the 7 rows has a primary key of "Gyu5A" and there is another row that has a primary key of "Gyu5a". These 7 rows are the only ones that have a lower case equivalent so I can only assume that the data comparison isn't taking in to account the collation of the column, which is case sensitive (specifically SQL_Latin1_General_CP1_CS_AS).