Case Sebsitive Data Comparisons
airvine
Posts: 5
I'm the writer/maintainer of a custom application our company uses to synchronize 4 versions of one of our larger databases. The app uses SQL Toolkit 5, and for the last year has been successfully used by our support personnel to perform thousands of maintenance operations.
Recently, a bug report appeared on my desk stating that a field in one of the tables being synchronized was not considered different by this application, even though there was a slight difference. The difference being that the field in the source database was the identical string to the destination database however the source database value had a capitalized first letter, and the destination database was all lowercase.
My company would like changes such as this to be discovered by the application and synchronized accordingly, however I am unable to find a property or comparison option that I can apply to the comparison session object that will ensure that case-sensitivity is enforced. Does such an option exist, or is there another method I can use to ensure that case changes are discovered as differences.
Thanks,
Al Irvine
Four Leaf Solutions Inc.
Recently, a bug report appeared on my desk stating that a field in one of the tables being synchronized was not considered different by this application, even though there was a slight difference. The difference being that the field in the source database was the identical string to the destination database however the source database value had a capitalized first letter, and the destination database was all lowercase.
My company would like changes such as this to be discovered by the application and synchronized accordingly, however I am unable to find a property or comparison option that I can apply to the comparison session object that will ensure that case-sensitivity is enforced. Does such an option exist, or is there another method I can use to ensure that case changes are discovered as differences.
Thanks,
Al Irvine
Four Leaf Solutions Inc.
Comments
That's a good question. If you're using v5 of the SQLDataCompare.Engine, it checks the column collation on the first database before performing a comparison of text fields. A case-sensitive column in database1 will cause SDC to compare case-sensitively, and a case-insensitive collation will cause it to compare without regard to case.
Typically, a case-sensitive collation will have a 'CS' in the collation name, and a 'CI' if it's case-insensitive.
If the collations are both CI, then you would need to change the column in the first database to be CS. If one is CI and the other CS, you can swap the first and second databases being compared and put the case-sensitive one as the first database.
The documentation says that you can't change a field collation in code (the property is read-only).
I wondered if the collation of the columns might be the culprit here. It seems I can pass this bug on to the DBAs.
Thanks for the quick reply.
Al Irvine
Four Leaf Solutions
FYI I believe SQL Toolkit v6 has an option to force a binary (case-sensitive) collation.