Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


change collation field


change collation field

Author
Message
pedro.ribeiro
pedro.ribeiro
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 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?
Ramesh Saive
Ramesh Saive
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2564 Visits: 2643
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


pedro.ribeiro
pedro.ribeiro
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 982
Can i lose some data, because of the change of collation?

Thanks,

Pedro
Ramesh Saive
Ramesh Saive
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2564 Visits: 2643
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


pedro.ribeiro
pedro.ribeiro
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 982
ok.

Thank you very much for your help,

Pedro
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search