COLLATE problem between SQL 7 and SQL 2000 databases

cook0002

I am comparing two databases, one on SQL 7 and one on SQL 2000. They contain a table which is indexed on a varchar(8) field. SQL Compare shows that the table on SQL 2000 differs from the one on SQL 7 in that it includes COLLATE SQL_Latin1_General_CP1_CI_AS.

When comparing the data in the two tables, records are shown as only existing on the source database and only existing on the destination database that have the SAME values in the primary key.

When I go into the Project Configuration window and the Tables & Views tab, the table is flagged with a warning symbol. If I mouse over the symbol, I am told 'Collations on the fields ... may vary, results may be unpredictable - consider using the Force Binary Collation option'. I flag 'Force Binary Collation' and re-compare only for the comparison to fail with the error 'Line 1 : Incorrect syntax near 'COLLATE' '

How do I get round this problem?

cook0002

... that's varchar(8) - I now know why there's an option to disable smilies.

cook0002

More information - it only seems to affect records with uppercase text

e.g.
A change in a record with primary key cook0002 will result in the record being flagged as different in the two databases.
A change in a record with primary key COOK0002 will result in the record being flagged as new in each database.

Brian Donahue

Hello,

I don't know what to suggest other than to DTS the table and data out into a SQL Server 2000 database, set the collation order, and compare this instead. SQL Data Compare performs collation-sensitive comparisons -- if you have a case-sensitive collation, then data is compared case-sensitively. If you have a case-insensitive collation, then text data is compared case-insensitively. Setting 'ignore case' and 'force binary collation' will not help affect the way that the text data is compared.

cook0002

I think I didn't explain myself properly and probably misled you slightly with my last posting.

Neither database treats textual data case sensitively.

Index text in lowercase matches correctly. Index text in uppercase does not match.

Brian Donahue

Hi,

Can you please use Enterprise Manager/Management Studio to script the table and see for sure what the column collation order is? If it's case-insensitive on both sides, this shouldn't happen!

cook0002

SQL 7 database has no collation set up against the individual columns.
The server settings (sp_helpsort) are:
Character Set = 1, iso_1
ISO 8859-1 (Latin-1) - Western European 8-bit character set.
Sort Order = 52, nocase_iso
Case-insensitive dictionary sort order for use with several We
stern-European languages including English, French, and German
. Uses the ISO 8859-1 character set.

SQL 2000 database has the following collation set up against the varchar columns:
COLLATE SQL_Latin1_General_CP1_CI_AS
The server settings (sp_helpsort) are:
Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data

I believe this means that both databases are case insensitive.

Brian Donahue

Hello,

Is this the latest version of Data Compare, 5.2.0.40?
Thanks!

cook0002

Yes it is.

Brian Donahue

Please try this: switch the registration order of the databases in the project settings so that the SQL Server 2000 is on the left and the SQL 7 server is on the right. Because SQL 7 does not return a collation order to SQL Data Compare, it should default to whatever database is on the 'left'.

cook0002

This does indeed remove the problem. I assume, though, that I cannot reverse the direction of the synchronization from right to left, so I'll just have to live with the comparison deleting and creating records rather than updating them.

cook0002

Just found out you CAN reverse the synchronization direction. Now we're cooking.

Brian Donahue

I don't think reversing the synchronization direction will help, but rather registering the databases with the SQL 2000 on the left, etc and then re-running the compare.

cook0002

I need to reverse the synchronization direction because my destination server is now on the left. If I still synchronize left to right, I will lose all my new data.

As far as I'm concerned, my problem now has an effective workaround.

Many thanks