Is it possible to do a bulk update command to alter all columns whose collation type is SQL_Latin1_General_CP1_CI_AS to Latin1_General_CI_AS?
We are getting collation conflict errors when trying to do cross database queries on a new database we have recently inherited.
I have changed the collation type of the databases to match, however there are individual columns in multiple tables that have their collation type set to SQL_Latin1_General_CP1_CI_AS, and this is still causing collation conflicts when querying these columns.
We have around 1000 columns with this collation type explicitly set so it's not practical to change them all individually. Is there a way of doing this in bulk?
If not, can anyone recommend an alternative method?
Don't know the answer to bulk changing them (export and reimport the data maybe? don't know how feasible that it).
We have explicit collation on every temporary table definition (e.g. in SProcs) and in every char comparison that compares data in different databases.
We have databases with collation that is different to the default on the server (and thus the collation in TEMPDB, hence the explicit collation on declaration of temp tables), and we have different collations in different databases, and this seems to solve the problem for us.
However, there is nothing to "police" that we didn't forget to put the collation in, so we could miss one (we rely on code review to catch this one ... not perfect!)
it can be a nightmare! We've in the process of "internationalising" (if that is a word then it's a business bullshit one!) now and it sounds like we were in the same boat as Kristen (forced collation on queries).
When you change the collation sadly you have to drop and recreate any keys referencing the column. That gets bad quick with compound keys. There are some scripts on the link I posted but your mileage may vary. Proceed with extreme caution and take backups.
Best of luck
Charlie =============================================================== Msg 3903, Level 16, State 1, Line 1736 The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Edited by - Transact Charlie on 01/21/2010 05:54:59
We use use VARCHAR for everything and ignore the issues of internationalisation. If someone can reliably enter an Accent, and get it displayed back to them (which for European characters seems to work OK) then fine. We have no current plans to support languages that need 2-byte character sets.