September 7, 2010 at 9:22 am
Hi friends,
I'm changing the column collation for some tables manually, I'm able to change the column collations using Management studio(right clicking on the column-modify) but when I run alter table script to change the collation I get check constraint errors..
Alter table zoom_in alter column type_fld varchar(30) collate Latin1_General_BIN
Msg 5074, Level 16, State 1, Line 1
The object 'type_fld_chk' is dependent on column 'type_fld'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN type_fld failed because one or more objects access this column.
Since this fails, I'm wondering is it correct to modify the column properties and change the collation in Management studio?
Thank you
September 8, 2010 at 2:50 pm
You cannot change the collation of a column that is currently referenced by any one of the following:
-A computed column
-An index
-Distribution statistics, either generated automatically or by the CREATE STATISTICS statement
-A CHECK constraint
-A FOREIGN KEY constraint
So in ordert o change the collation you'll have to drop the check constraint, change the collation then recreate the check constraint. Additionally, you may have to drop a statistic if one was auto generated.
September 17, 2010 at 8:43 am
I believe, in some instances, you can make changes in the GUI that T-SQL does not enable you to make. You can try using the GUI, but don't be surprised if it gives you a similar error upon saving. And if it does save, be aware that it will save changes to multiple objects.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply