Case Sebsitive Data Comparisons

Forum for users of SQL Toolkit 3,4,and 5

Case Sebsitive Data Comparisons

Postby airvine » Fri Feb 08, 2008 7:39 pm

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.
airvine
 
Posts: 3
Joined: Mon Mar 26, 2007 8:28 pm

Postby Brian Donahue » Sat Feb 09, 2008 11:34 pm

Hi Al,

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).
Brian Donahue
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am

Postby airvine » Mon Feb 11, 2008 2:19 pm

Hi Brian,

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
airvine
 
Posts: 3
Joined: Mon Mar 26, 2007 8:28 pm

Postby Brian Donahue » Mon Feb 11, 2008 2:28 pm

Hi Al,

FYI I believe SQL Toolkit v6 has an option to force a binary (case-sensitive) collation.
Brian Donahue
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Toolkit Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests