May 12, 2009 at 4:00 am
Hi - I need to change a collation of a field.
The field is varchar(20).
I need to change it, from SQL_Latin1_general_cp1_CI_AS to latin1_general_ci_as.
Someone created this field with this collation, but i need to put the collation equal to the other tables.
This field as only data , like this : s4.01-12312 or like this 0000233
Can i lose data when changing the field collation?
tks,
Pedro
P.S when changing the collation of this field, i receive the folowing message:
Server: Msg 5074, Level 16, State 8, Line 1
The index 'Indice_RGC' is dependent on column 'RGC'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN RGC failed because one or more objects access this column.
Do i have to disable the index and then enable it after the collation alter?
May 12, 2009 at 4:35 am
To change the collation of a column which is a part of an INDEX, your have to re-create the INDEX(es)
IF ( OBJECT_ID( 'tempdb..#test' ) IS NOT NULL )
DROP TABLE #test
CREATE TABLE #test( SomeCol VARCHAR(10) COLLATE SQL_Latin1_General_CP1_CI_AS )
CREATE INDEX IX_1 ON #test( SomeCol )
DROP INDEX IX_1 ON #test
ALTER TABLE #test ALTER COLUMN SomeCol VARCHAR(10) COLLATE Latin1_General_CI_AS
CREATE INDEX IX_1 ON #test( SomeCol )
--Ramesh
May 12, 2009 at 6:22 am
Can i lose some data, because of the change of collation?
Thanks,
Pedro
May 12, 2009 at 6:49 am
Don't worry, you wont lose any data if you change the collation. But since this behaviour hasn't been documented very well, I suggest you take the backup of the table/database before changing the collation.
--Ramesh
May 12, 2009 at 6:55 am
ok.
Thank you very much for your help,
Pedro
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply