Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nanite2000
Starting Member
3 Posts |
 Posted - 2010-01-20 : 22:55:33
|
| Hi,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?Thanks for any help you can offer.Rob.SilverBolt.net Developmenthttp://www.silverbolt.net |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-21 : 02:49:09
|
| 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!) |
 |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-01-21 : 05:53:51
|
| 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).Peso started a blog article on the subject : http://weblogs.sqlteam.com/peterl/archive/2009/08/19/Change-collation.aspx but he never finished it.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 luckCharlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-21 : 06:10:47
|
| 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. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-01-21 : 06:28:27
|
| We've just moved into china.............Joy unbounded!Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-21 : 06:42:39
|
No problem:REPLACE(REPLACE(syscomments, 'varchar', 'nvarchar'), 'nnvarchar', 'nvarchar')  |
|
|
|
|
|
|
|
|
Change Collation Type Of Multiple Columns