SQL Server Performance Forum – Threads Archive
How can i change collation of userdefined datatype
<br />Hi everyone?<br /><br />How can i change collation of userdefined datatype?<br />When i try to change it Management Studio Query<br /><br /><br />varcharUzun = varchar(100) –> userdefined data type….<br /><br /><pre id="code"><font face="courier" size="2" id="code">ALTER TABLE tbxxxx ALTER COLUMN sDefinition varcharUzun COLLATE Turkish_CI_AS NOT NULL </font id="code"></pre id="code"><br /><br />when i run the query it says.<br /><pre id="code"><font face="courier" size="2" id="code"><br />Msg 452, Level 16, State 1, Line 2<br />COLLATE clause cannot be used on user-defined data types.<br /></font id="code"></pre id="code"><br /><br /><br />i also cannot ALTER the userdefinedtypes collation itself. And i canyt chance the collation if the column is PrimaryKey [<img src=’/community/emoticons/emotion-6.gif’ alt=’
‘ />]<br />Is there any other to change the collation without dropping and re-creating the objects.<br /><br />Thanks.<br /><br /><br />
Do you have to use a user-defined datatype? Have you tried changing to a simple varchar(100) with the collation you want? Changing collation in general is a pain.
<br />thanks thomas but i have to.<br />i have already used it about 200 tables lots of records <img src=’/community/emoticons/emotion-6.gif’ alt=’
‘ /><br />very big pain in data transfer.
Yes, it is a pain..Changing the column collation has some restrictions too… You have to handle the temp table created in tempdb also when you have different collation at db level… Setting and Changing the Column Collation
http://msdn2.microsoft.com/en-us/library/ms190920.aspx
MohammedU.
Moderator
SQL-Server-Performance.com
]]>