Problem with ForceBinaryCollation option

mmchenry

I'm having a problem using the ForceBinaryCollation option on the command line. I first had the problem in 9.0, but I upgraded to the latest v10.2.3.5 and the problem still exists.

I'm using a scripts folder source and a SQL2008 DB as the target. I want the data comparison to be case sensitive, so I've specified the ForceBinaryCollation option on the command line. When the ForceBinaryCollation option is used the comparison does not notice a new record in one of the tables that exists only in the source. It thinks the source and target are identical. If I remove the ForceBinaryCollation option, it sees the new record and syncs it to the target correctly.

Here's a sample of the command line I'm using:
sqldatacompare.exe /Scripts1:MyScriptsFolder /Server2:MyServer /Database2:MyDatabase /ScriptFile:MyScriptFile.sql /Force /Synchronize /Options:ForceBinaryCollation /Export:MyExportFolder

If I do the same comparison via the GUI, it always sees the new record in the source, regardless of whether I check the Force Binary Collation check box or not, which is what I would expect. I'm not sure why the command line is different.

Has anyone else seen this behavior? Am I doing something wrong or is this a bug?

James B

Hi,

I had a look through our bugtracker but I couldn't find the specific scenario you describe.

It may be a bug; would it be possible for you to send over both the create script for the table from the DB, along with the files relating to the same table from the Tables and Data folders in your scripts? I'd like to see if I get the same behaviour here, and if so, log a bug.

If you can send it to support@red-gate.com with F0065780 in the subject line, it would be great, thanks.

mmchenry

Thanks. I emailed the requested files with details of how to replicate the scenario.

James B

Cheers for that.

I initially got the same result, but it's not actually looking like a bug. When you specify anything in the "options" switch, it'll remove the default options we assume. So by specifying /options:forcebinarycollation, ALL other options were turned off. If you go into the GUI and de-select all the options asides from that one you get the same effect. I think the problem then occurs because the Identity PK is ignored (the GUI is more helpful in this point, explaining it cannot actually compare the table, as opposed to not finding a difference).

The solution is to ensure you specify default too, i.e.:

/options:default,forcebinarycollation

mmchenry

OK, I feel stupid now. :oops:

I see that in the documentation now. I'm not sure it's a typically expected behavior for command line tools, but I guess it's my bad for not reading more carefully.

Thanks for the help.