Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

change collation field Expand / Collapse
Author
Message
Posted Tuesday, May 12, 2009 4:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, May 31, 2009 10:19 AM
Points: 103, Visits: 982
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?



Post #714822
Posted Tuesday, May 12, 2009 4:35 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 21, 2013 12:32 AM
Points: 2,556, Visits: 2,593
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

Post #714845
Posted Tuesday, May 12, 2009 6:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, May 31, 2009 10:19 AM
Points: 103, Visits: 982
Can i lose some data, because of the change of collation?

Thanks,

Pedro
Post #714903
Posted Tuesday, May 12, 2009 6:49 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 21, 2013 12:32 AM
Points: 2,556, Visits: 2,593
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

Post #714950
Posted Tuesday, May 12, 2009 6:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, May 31, 2009 10:19 AM
Points: 103, Visits: 982
ok.

Thank you very much for your help,

Pedro
Post #714960
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse