March 31, 2005 at 2:19 am
Hi All,
I have a database with a default collation set in SQL 2000. I want to change the collation to SQL_Latin1_General_CP1_CS_AS from SQL_Latin1_General_CP1_CI_AS'
I run the following command in QA :
'alter database testdb collate SQL_Latin1_General_CP1_CS_AS'
I get the following errors in QA.
Server: Msg 5075, Level 16, State 1, Line 1
The object 'mbt_function_fn_4' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The column 'mbt_function_fn_4.fname' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The column 'mbt_function_fn_4.lname' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The column 'mbt_function_fn_4.title' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The object 'CK__mbt_everythin__c__00DF2177' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The object 'mbt_constraint_1' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The column 'mbt_integer_tb.tcomp' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The column 'mbt_smallint_tb.tcomp' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The column 'mbt_tinyint_tb.tcomp' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The column 'mbt_money_tb.tcomp' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The column 'mbt_smallmoney_tb.tcomp' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The column 'mbt_real_tb.tcomp' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The column 'mbt_char_tb_1.charxxx' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The object 'mbt_indexed_view_vw_1' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The object 'mbt_indexed_view_vw_2' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The column 'mbt_char_tb_3.charxxx' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The column 'mbt_char_tb_5.charxxx' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The object 'CK__mbt_view___mgrid__1CBC4616' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The column 'mbt_varchar_tb_1.varcharxxx' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The object 'CK__mbt_view___mgrid__1EA48E88' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The column 'mbt_varchar_tb_3.varcharxxx' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The column 'mbt_varchar_tb_5.varcharxxx' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The column 'mbt_nchar_tb_1.ncharxxx' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The column 'mbt_nchar_tb_3.ncharxxx' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The column 'mbt_nchar_tb_5.ncharxxx' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The column 'mbt_nvarchar_tb_1.nvarcharxxx' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The column 'mbt_nvarchar_tb_3.nvarcharxxx' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The column 'mbt_nvarchar_tb_5.nvarcharxxx' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The column 'mbt_bin_tb_1.binxxx' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The column 'mbt_bin_tb_3.binxxx' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The column 'mbt_bin_tb_4.binxxx' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The column 'mbt_varbin_tb_1.binxxx' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The column 'mbt_varbin_tb_3.binxxx' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The column 'mbt_varbin_tb_4.binxxx' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The column 'mbt_bigint_tb.tcomp' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The column 'mbt_decimal_tb_1.tcomp' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The column 'mbt_numeric_tb_1.tcomp' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The column 'mbt_float_tb_1.tcomp' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 1
The object 'CK__mbt_tb_3__mgrid__7D78A4E7' is dependent on database collation.
Server: Msg 5072, Level 16, State 1, Line 1
ALTER DATABASE failed. The default collation of database 'testdb' cannot be set to SQL_Latin1_General_CP1_CS_AS.
How do I deal with this problem ?
This is just a test database. So one way I could do is create the database with the collation I want. But if I already had a live database, and I wanted to change the collation, then I get stuck with these errors. Please help.
--Kishore
March 31, 2005 at 10:05 am
When you change the database collation it does not recollate the existing objects.
To recollate a database the safest method is to create a copy of your database to be recollated, create a new empty database with the correct collation and name, script the objects from your old database ( making sure not to take any collation statements ) and recreate them in your new database. Move the data, recreate your users and permissions.
Be careful of attempting to dts the lot as you'll find you get the old collation for the objects in your new database.
You can modify the system tables ( which is much quicker ) but this is unsupported and anyway you shouldn't do that sort of thing as it's risky - if it's test database maybe though.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
April 1, 2005 at 5:30 am
Hi,
i would recommend
http://www.sqlservercentral.com/columnists/mpearson/changingroguedatabasecollations_printversion.asp
if your tables have a lot of columns you might run into trouble with the script for copying data. In that case i can post an adapted script for that job...
rgards karl
Best regards
karl
April 1, 2005 at 7:08 am
I have found an approach similar to Colins to fastest and easiest. I recently had to change the collation sequence on two diffrent data warehouse databases. What I did was script the entire database (users, objects, keys, everything) and then edit the script in QA and change all the collation sections to be the collation I wanted. (simple search and replace) Then run the script on a separate server. That will create the database as a shell. Then import data from the original database to the new using DTS. Providing you created the database first, DTS will not bring in the original collation as it will use the existing one on the new database. Once you are done doing that you can detach the original and attach the new one.
April 6, 2005 at 5:20 am
I uses the same logic (I have found an approach similar to Colins to fastest and easiest. I recently had to change the collation sequence on two diffrent data warehouse databases. What I did was script the entire database (users, objects, keys, everything) and then edit the script in QA and change all the collation sections to be the collation I wanted. (simple search and replace) Then run the script on a separate server. That will create the database as a shell. Then import data from the original database to the new using DTS. Providing you created the database first, DTS will not bring in the original collation as it will use the existing one on the new database. Once you are done doing that you can detach the original and attach the new one. )
This is fast and better option.
April 6, 2005 at 5:21 am
Thanks guys for all the help.
March 5, 2007 at 2:08 pm
I executed
ALTER DATABASE Test COLLATE Latin1_General_BIN
I donot get any errors. How do i check if database changed to new Collation.
March 25, 2008 at 10:38 am
Hello all.
The link above:
http://www.sqlservercentral.com/columnists/mpearson/changingroguedatabasecollations_printversion.asp
does not work. I am interested in seeing this document/column/white paper if it still exists on the SQLServerCentral site. Can someone please tell me how to get access to it?
Thanks,
Dan McCue
April 8, 2008 at 1:32 pm
Never mind the last question, found it here:
http://www.sqlservercentral.com/articles/Administering/changingroguedatabasecollations/994/
Dan
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply