How can i change collation of userdefined datatype

[karbon]

Hi everyone?

How can i change collation of userdefined datatype?
When i try to change it Management Studio Query


varcharUzun = varchar(100) --> userdefined data type....

ALTER TABLE tbxxxx ALTER COLUMN sDefinition varcharUzun COLLATE Turkish_CI_AS NOT NULL

when i run the query it says.

Msg 452, Level 16, State 1, Line 2
COLLATE clause cannot be used on user-defined data types.

i also cannot ALTER the userdefinedtypes collation itself. And i canyt chance the collation if the column is PrimaryKey []
Is there any other to change the collation without dropping and re-creating the objects.

Thanks.

 

[ptheriault]

Karbon,
You should be able to change the collation when you select the data.

SELECT sDefinition collate Latin1_General_CS_AS
FROM tbxxxx

You may need to convert it to varchar.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[karbon]

thanks ptheriault i know

but
in your example you just change the collation of the result.

Table's Column COLLATION dont change.
i want to change the TABLE STRUCTURE

ALTER TABLE tbxxxx ALTER COLUMN sDefinition varchar(100) COLLATE Turkish_CI_AS NOT NULL

no problem.

ALTER TABLE tbxxxx ALTER COLUMN sDefinition xxxuserdatatype COLLATE Turkish_CI_AS NOT NULL

problem

 

[ptheriault]

Karbon,
As far as I know you can not drop or alter a user defined data type thet is in use.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[karbon]

thanks

 

[ptheriault]

As a work around you could alter your table like this.

SELECT * INTO NEW_TABLE FROM OLD_TABLE
WHERE 1=2

Drop the User defined column in NEW_TABLE and re-add with your collation.

Insert all data from OLD_TABLE

Rename OLD_TABLE to something OLD_TABLE_OLD

Rename NEW_TABLE to OLD_TABLE

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

I forgot to add, the select * into syntax will only create your table. You will need to re-add any indexes, triggers, or contraints that you had on the old table.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[karbon]

i understand, and know the method.
BSA(BacktoStoneAge) method. ))

Thanks a lot but this database is live.
and it has millions of records and PrimaryKey tables and keys and indexes triggers. So i cant stop the engine.

I can easily change the COLLATION with normal datatypes, i only have problem with UDDTypes and also
YOU cannot change the COLLATION of PrimaryKey Columns.

Anyway thank you very much for your replies.

 

[ptheriault]

No problem. Sorry it wasn't of any use.


- Paul
- If at first you don't succeed, find out if the loser gets anything.