COLLATE problem between SQL 7 and SQL 2000 databases

cook0002cook0002 Posts: 20
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?

Comments

  • ... that's varchar(8) - I now know why there's an option to disable smilies.
  • 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 DonahueBrian Donahue Posts: 6,590 New member
    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.
  • 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 DonahueBrian Donahue Posts: 6,590 New member
    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!
  • 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 DonahueBrian Donahue Posts: 6,590 New member
    Hello,

    Is this the latest version of Data Compare, 5.2.0.40?
    Thanks!
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    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'.
  • 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.
  • Just found out you CAN reverse the synchronization direction. Now we're cooking.
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    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.
  • 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
This discussion has been closed.