Char(n) columns not comparing correctly in

NeedyInfoNeedyInfo Posts: 3
When I compare table A in database A to the exact same table (structure and data) table A in database B the char(n) columns do not compare correctly. Data Compare reports differences in about 10% of the rows even though the data in the rows are exactly the same. This is between database A and database B on the same sql2005 server both with compatibility set to 9.0. When I check the strings with TSQL functions Difference(), Datalength() and Len() the string functions return the exact same result. Is this a bug in Data Compare??


  • Options

    There's two things to check: firstly, have a look in the textual difference viewer (double click on one of the "different" values). This should give you a better idea of exactly where we think the data differs, which might give you some more clues.

    Secondly, have you checked whether the two columns are the same collation on both databases? This can certainly affect what's considered equal.

    Hope that helps,
    Robert Chipperfield
    Red Gate
  • Options
    Looks like checking "trim trailing spaces" under Edit Project took care of the problem. Still not sure why Table A Char(6) would only show 3 chars for XXX in the column while Table B would show 6 chars for XXX. It is correct to show 6 chars for a Char(6) column. It's almost like Data Compare was seeing some rows in Table A as Varchar(6) because it was correctly showing 90% of the rows correctly. Thanks for your help. Love the compare products!!
Sign In or Register to comment.