November 7, 2008 at 9:37 am
ALTER TABLE dbo.table1 ALTER COLUMN [EmpID] [varchar](16) COLLATE Latin1_General_CI_AI
this gives an error.
Msg 5074, Level 16, State 1, Line 1
The index 'index_M' is dependent on column EmpID.
Msg 5074, Level 16, State 1, Line 1
The index 'index_D' is dependent on column EmpID.
Msg 5074, Level 16, State 1, Line 1
The index 'index_S' is dependent on column EmpID.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN EmpID failed because one or more objects access this column.
when i try
ALTER INDEX ALL ON table1 DISABLE
ALTER TABLE dbo.table1 ALTER COLUMN [EmpID] [varchar](16) COLLATE Latin1_General_CI_AI
ALTER INDEX ALL ON table1 REBUILD
I get an error saying
Collation can not be changed because clustered index is disabled.
Does any one jumped into such problems? any solution
November 7, 2008 at 10:02 am
It looks to me like you'll be dropping and recreating indexes.
I can't see a way to avoid it.... test it first.
~BOT
Craig Outcalt
November 7, 2008 at 10:08 am
Same with me - anytime I need to change collations I have to drop all the indexes and constraints first and then recreate them afterwards. If you have a lot of them then it's best to script them out first.
November 10, 2008 at 6:39 am
For changing collation, how about addinga new DB like dummy and set collation for that as needed and then import all the data from the source DB, how efficeint does this process work for changing collation on whole DB,tables and columns.
thanks
November 11, 2008 at 6:55 am
what is the impact if system databases and the user databases have different collations.
I found that all my system db's are differnt from many user db's, is it mandatory changing the collation of system db's. Many user db's has the collation which is stabdard in our environment but sys db's are different so want to make it standard but wud like to know its impact if am not changing them.
November 11, 2008 at 7:28 am
Generally there's no issue having different collations on your system database unless you're running queries between them and differently collated user databases. This usually comes about as a result of trying to join queries to temporary tables created in tempdb. Changing system collations is a pain involving either reinstalls, rebuildm (SQL 2000) or other such commands and if your applications allow it then changing the collation of the user databases is easier.
Personally if I notice any collation differences then unless there's an issue I tend to leave them. Bit reactive I know but.....
November 11, 2008 at 7:58 am
For changing collation just for 1 db i need to drop,create indexes and constraints. Through SSMS i can script create indexes at once but not drop. is there any script which does drop indexes,constraints and recreated indexes,constraints.
November 11, 2008 at 8:11 am
This is the script that I use and it does most things for you.
http://www.databasejournal.com/scripts/article.php/1501031/spIndexKeyTextsql.htm
create the stored procedure in the database in question and then run:
"sp_IndexKeyText 26" to generate primary key constraints
"sp_IndexKeyText 28" to generate foreign key constraints
"sp_IndexKeyText 25" to generate indexes
It should create both the create and drop scripts for you. It needs a bit of tweaking for SQL 2005 but nothing too difficult.
cheers
November 11, 2008 at 8:18 am
do i need to change anything to run in sql 2005
November 11, 2008 at 8:38 am
You can run the script straight into 2005 and it will produce drop and create statements for you. The drop statements should work straight away. The create statements may need a little tweak. For example fill factors of 0 are no longer allowed but you can easily amend this. I also think there's a comma missing from the create foreign key constraints script but once you have the script you can again easily fix this.
My advice is give it a go and see what you get out.
cheers
November 11, 2008 at 1:55 pm
Instead of changing collation on existing DB how abt this..
1)create a new db with collation required and move data over to the new DB
Or
2) is there a way i can use backup and change collation while doing a restore.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply