| Author |
Topic |
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
 Posted - 2005-03-17 : 06:08:52
|
| Hi all,We have noticed on a test server the "Fatal error 644" problem, and after some searching, found: http://support.microsoft.com/kb/822747Which pretty much pointed us to the problem. After doing a DBCC DBREINDEX on the specifc problem table, the inital instance of the problem has been resolved.In addition, it was determined that the specif problem existed due to an explicit collation specified on a column that is indexed. this should not be there (legacy).Now, obviously I can drop and recreate the table correctly (collation of columns are not a problem in terms of the data, so we wouldn't have data loss concerns). What I am trying to find out is whether I could do an ALTER TABLE to remove an explicit collation. Reviewing BOL I believe I can add one, but how do I remove one?ALTER TABLE tbALTER COLUMN x COLLATION default^-- syntax doesn't work - is there a correct way?Thoughts, better solutions?*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-03-17 : 06:11:15
|
quote:
Originally posted by Wanderer
Hi all,We have noticed on a test server the "Fatal error 644" problem, and after some searching, found: http://support.microsoft.com/kb/822747Which pretty much pointed us to the problem. After doing a DBCC DBREINDEX on the specifc problem table, the inital instance of the problem has been resolved.In addition, it was determined that the specif problem existed due to an explicit collation specified on a column that is indexed. this should not be there (legacy).Now, obviously I can drop and recreate the table correctly (collation of columns are not a problem in terms of the data, so we wouldn't have data loss concerns). What I am trying to find out is whether I could do an ALTER TABLE to remove an explicit collation. Reviewing BOL I believe I can add one, but how do I remove one?ALTER TABLE tbALTER COLUMN x COLLATION default^-- syntax doesn't work - is there a correct way?Thoughts, better solutions?*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here!
try COLLATE instead of COLLATION--------------------keeping it simple... |
 |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2005-03-17 : 06:13:43
|
Hmm - typo - I did try collate ... no joy:quote:
Server: Msg 156, Level 15, State 1, Line 2Incorrect syntax near the keyword 'default'.
*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-03-17 : 06:18:35
|
this might help...quote:
ALTER COLUMN cannot have a collation change if any of the following conditions apply: If a check constraint, foreign key constraint, or computed columns reference the column changed. If any index, statistics, or full-text index are created on the column. Statistics created automatically on the column changed will be dropped if the column collation is altered.If a SCHEMABOUND view or function references the column.
--------------------keeping it simple... |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2005-03-17 : 06:29:32
|
| Thanks jen - I had seen that. the problem is I cannot find the specific syntax, or an example, for removing a collaiton.I would rather not (although I have started down the route) of coding:-the removal of all FK's pointing to table, -removal of all constraints on the table, -dropping of all indexes on the table, -creating a temporary table (without the collation on the revelant column), -transferring data from current table to temp, -dropping current table, -re-creating all constraints, -recreating all indexes, -recreating foreign key's that reference the table These are all activities it seems I will have to do if I can simply remove the collation.*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
kish
Starting Member
45 Posts |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2005-03-17 : 08:46:52
|
| Thanks for the response Kishore!From all that I've read so far (your link confirmed what I had already read) it would seem that the Alter table can only be used to allocate a specific collation, and not remove a prevously explicitly specified one.i.e.If I want to get rid of the collation, I have to replace it with an explicitly chosen one.Thanks again Kishore and Jen for the responses...*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-03-17 : 14:15:51
|
| I haven't checked but can you replace the existing collation with a "default" collation?Perhaps that wouldn't solve the problem anyway?Kristen |
|
|
|
leahsmart
Posting Yak Master
133 Posts |
Posted - 2005-08-17 : 05:33:51
|
| Did you find a way to replace the collation with the database default? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-17 : 06:15:09
|
| If you DON'T put a COLLATE statement on the column definitions in your CREATE TABLE statement it will do that, won't it?Kristen |
|
|
|
leahsmart
Posting Yak Master
133 Posts |
Posted - 2005-08-17 : 06:25:47
|
| the tables already exist, I need to change it to the database default |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-17 : 06:38:21
|
| I don't think you can do that in situ, create a temporary table, copy the data across, drop the original table and then rename the "temporary"If you do it in Enterprise Manager it should do those steps for you (or it will create a script for you to use, if you prefer)Kristen |
|
|
|
zeecees
Starting Member
1 Post |
Posted - 2008-11-19 : 04:54:39
|
quote:
Originally posted by leahsmart
the tables already exist, I need to change it to the database default
quote:
Originally posted by KristenI haven't checked but can you replace the existing collation with a "default" collation?Perhaps that wouldn't solve the problem anyway?Kristen
Guys its simple....Just write an "ALTER TABLE ALTER COLUMN" query, for the column u want adding all its properties and omiting the collation. then you have altered it to the default collation.for example:I have a column :NAME nvarchar(10) NOT NULL COLLATION LATIN (can;t remember a correct collation atm)to change it back to the default db collation all you have to do is the following:ALTER TABLE (table name) ALTER COLUMN NAME NVARCHAR(10) NOT NULL (ommiting the collation option)OFC you need to have the same or bigger column size to keep you existing data intact.  Yo! |
|
|
|
|
Remove column collation