case sensitive with data compare

reb

Data Compare do not see the case differences.
I compare two tables with the same texte but with not the same case and datacompare see no difference.

I would like datacompare do the update with case sensitive. it's possible ?

I have unchecked the checkbox option "ignore case" but nothing change. I know there is an SQL option to ignor case(default state with sqlserver 2000) in sort but it will shame if Datacompare use it.

Thanks a lot for your Help !

Brian Donahue

Hello Regis,

Thanks for asking. In Data Compare 5, there is an 'ignore case' option, but this only applies to the mappings of the table schema. The option that you need is 'force binary collation'. This will essentially convert any strings to their underlying raw bytes, which is going to result in letters of different case being seen as different.

geno.prida

I am seeing the same behavior and the above does not solve the issue.

Are there any known issues where SQL Data Compare does not see a difference in a varchar field that is just case? This is not a key field, just a simple varchar field.

Any Ideas?

ewuphoff

Does anyone know of a resolution to this? I am having the same problem on a varchar (50) field. The schema is exactly the same, I have "force binary collation" checked, the compare shows all rows identical on compare and viewer.

Running SQL 2005. Any help would be appreciated.

One specific problem : 'Variable Life Products' vs. 'Variable LIfe Products'

David Atkinson

Hi,

A way to achieve a case-sensitive comparison is to temporarily change the collation of the column to its case sensitive version. You should only need to change the collation for the left-hand-side column. Take note of the column's collation so you can change it back afterwards.

For example

-- To find the collation for all columns in a table
SELECT name, collation FROM syscolumns WHERE [id]=object_id('CI_TestTable')

USE LHS_DATABASE
ALTER TABLE CI_TestTable ALTER COLUMN [myvarcharcol] VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS NULL

Now do your data comparison (if you have SQL Data Compare open, you'll need to click the refresh button in the Project Configuration Tables & Views tab to refresh the schema) and then perform the synchronization. Afterwards change the column back to the original case insensitive collation:

ALTER TABLE CI_TestTable ALTER COLUMN [myvarcharcol] VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

Let me know if this doesn't work for you,

Best regards,

David Atkinson
Red Gate Software

nicka-p3

Well I was able to fix the issue I initially posted here about, but there was a curious issue I noticed while trying to fix it:

SQL Data Compare 5 version 5.3.0.68 does not see a difference in table data within CI varchars, even with binary collation on..

But SQL Data Compare 3.3.8.304 does see the difference. Just seems curious to me. Incidentally, the initial problem I was having was between the values 'PhD' and 'PHD'.

dhsjphd

Thanks, David, for the solution. Is this going to be "fixed" in later releases or is this a SQL Server issue beyond the scope of Red Gate's software?

I am currently using 5.4.0.59 and it seems from nicka's post that the functionality in 3.3.8.304 is what is desired.

richardjm

We're hoping to put in an option in version 6 to override the collation based comparison of the data. There should be an option to force the case sensitivity of the string comparisons regardless of the collation your string is using.

We'll also be re-wording some of the other options to make it clearer what they actually do.

There'll be an Alpha release of version 6 soon but I don't envisage this feature making it before the Beta, the Alpha mainly being released so that people can experiment comparing to backup files.

Hope this helps.

Richard

PS If you want to be put on the list of people to be contacted about the Alpha just drop me an email at richard.mitchell@red-gate.com and I'll make sure you're kept up to date.