Changing Collation of Database

Question

Changing Collation of Database

MasterDB

Hall of Fame

Points: 3054
More actions
October 17, 2011 at 10:42 am

#249430

Hi Folks,
I've an instance which is in Chinese_PRC_90_CI_AS Collation and i've a backup of a database which is in SQL_Latin1_General_CP1_CI_AS Collation.
I tried to Change the collation after restoring the database with
ALTER DATABASE [DATABASENAME] COLLATE Chinese_PRC_90_CI_AS
this trows an error like some objects are in dependency.
Now, how can i restore that database into Chinese_PRC_90_CI_AS collation.?
TIA..

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

David H. SSC Enthusiast Points: 187 More actions · Answer 1

Uh oh. Collation.

Well, there's not really a happy, shiny answer for you on this. This is why my teeth itch when people monkey with collation (and they don't have to). Not saying you don't have to, of course. 🙂

Anyway...

You cannot really alter collation on an entire database all at once with an ALTER db. Any ALTER statement to change a database's collation is only going to change said collation for newly created objects. Also you may run into dependency errors (as you in fact did). You can attempt to go in and execute ALTER table statements to change the collation of individual tables and try to walk through this piecemeal, but honestly since with collation you're talking about how the data is actually stored and parsed in the tables your best bet is to script out a table by table export of the data, script out your keys, etc... export it all out and import it all in to another database that has the correct collation. Then setup all your keys, relations and go.

That's if you want to fix this database-side. If there are technical or business reasons you cannot do this, another option is to alter the transactions that are thrown against said database to include COLLATION clauses, but that also can be problematic depending on scale.

Lowell SSC Guru Points: 323518 More actions · Answer 2

i went down this rabbit hole once or twice;

I started building a catch all script that changed everything i could think of...so if a column changed from [SQL_Latin1_General_CP1_CI_AS] to [Latin1_General_CI_AS], i was building a script that fixed all the related constraints, defaults, foreign keys..etc, before finally fixing the column itself. gosh it goes on and on; i never finished it because it's not a small project.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

MasterDB Hall of Fame Points: 3054 More actions · Answer 3

Thanks for your inputs Lowell.

Today i've created an empty database with Chinese_PRC_90_CI_AS collation and i tried to copy the data which is in SQL_Latin1_General_CP1_CI_AS Collation anyway doing this was not successful. i got the below error.

ERROR : errorCode=-1071636471 description=An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "OLE DB provider 'STREAM' for linked server '(null)' returned invalid data for column '[!BulkInsert].AREANAME'. ".

helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC} (Microsoft.SqlServer.DtsTransferProvider)

Anyhow some objects are copied but those are not sufficient to me..

Appreciate can anyone provide their inputs to fix this problem..

David H. SSC Enthusiast Points: 187 More actions · Answer 4

Two things off the cuff.

1. Usually when SQL tells me I'm trying to connect to null it makes me edgy (or connect AS null as well). But if you're using SSIS and this is an object you've been otherwise pumping data into that's prolly not it at all.

2. Check your data typing on that column it mentions in the destination and be sure you're not running into auto-casting issues converting data types between source and destination.