SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Change Collation Type Of Multiple Columns
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nanite2000
Starting Member

New Zealand
3 Posts

Posted - 01/20/2010 :  22:55:33  Show Profile  Reply with Quote
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 Development
http://www.silverbolt.net

Kristen
Test

United Kingdom
22415 Posts

Posted - 01/21/2010 :  02:49:09  Show Profile  Reply with Quote
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!)
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 01/21/2010 :  05:53:51  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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 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
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 01/21/2010 :  06:10:47  Show Profile  Reply with Quote
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.
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 01/21/2010 :  06:28:27  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
We've just moved into china.............

Joy unbounded!


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 01/21/2010 :  06:42:39  Show Profile  Reply with Quote
No problem:

REPLACE(REPLACE(syscomments, 'varchar', 'nvarchar'), 'nnvarchar', 'nvarchar')

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000