False(?) Collation Warnings

canter

I have a number of SQL Server instances with a instance collation of SQL_Latin1_General_CP1_CI_AS. Each of these instances has a database with a database collation of SQL_Latin1_General_CP1_CS_AS.

When I compare two SQL Server 2000 databases using SQL Data Compare 5.1.0.106, I receive no warnings. However, when I compare a 2000 database with a 2005 database, I get warnings on each text column claiming that the Collations on the two columns vary (when, in fact, they do not).

When I ignore the warnings, it seems to work correctly, but I'm confused by the warnings. Is there something different in the way Collations are used in 2005, or does SQL Data Compare do something differently? Maybe it's mistakenly querying the instance instead of the database for the collation in either 2000 or 2005?

Thanks,

Mike Canter

Brian Donahue

Hello Mike,

Is the issue that collations are specified for columns in one database, while no collations are specifically given to columns in the other, but the default collation is the same as the specific collation on the columns of the first database?

I think that in this case, you would end up with a difference, but using the 'ignore collations' option should disregard this as a difference.

canter

I figured out the problem - the database copy utility in SQL Server 2005 does not appear to maintain the collation of the database even if it is not set to be the SQL Server Instance default. All is well now (though I had to reinstall SQL Server to get it right!). Thanks for your help.

Brian Donahue

Hi Mike,

Cool, thanks!