Synchronization and database default collation

kaa

Hi,

I have two databases that I want to syncronize to an extent. Database A has one database default collation and database B another. A table in database A has fields with their collation set to <database default>. When i synchronize the databases Sql Compare lists the inherited collation of the fields explicitly (in my case SQL_Latin1_General_CP1_CI_AS) instead of <database default>. When generating scripts, this inherited collation is given explicitly in the TSQL statements which overwrites the collation of the fields in database B which means they are no long collated according to database B's default collation.

It seems to me that Sql Compare does not support <database default> collations very well. Is there something I'm doing wrong?

I am using an older version of Sql Compare (7.1), but I would like to know if the newer version has better support for this before upgrading.

Chris Auckland

Thanks for your post.

Have you tried the project option 'Ignore Collations'? It sounds like it should do what you need.

Edit Project > Options > Ignore > Collations

I hope this helps.

kaa

Hi,

Thank you for replying, 'ignore collations' is not quite what I'm looking for. I do want to compare collations, I just want to ensure the collation is <database default> on both databases.

I realize it, and collations in most usages, is sort of an edge case but it is also a potential silent dataloss situation so I was wondering if it is something you guys were aware of.

Chris Auckland

Thanks for your reply.

I'm afraid SQL Compare doesn't compare the database level collation, only the object level collation.

If you don't want to make the column level collation the same in the source and the target, the only option is to ignore them completely.

If you ignore the collation and a new object is created, SQL Server will use the database default collation for the column.