September 20, 2002 at 5:40 pm
I'm working through trying to change the character sets for one of my databases. To do this I'm changing the character set for each affect field. I've run across a small problem. Seems like BOL says you can alter the collate information associated with a text field, but I can't seem to get it to work. Here is what I am trying to do. See anything wrong with this alter table alter column command:
create table x(a text)
alter table dbo.x
alter column a text
collate SQL_Latin1_General_CP1_CI_AS
drop table x
Here is the error I am getting:
Server: Msg 4928, Level 16, State 1, Line 3
Cannot alter column 'a' because it is 'text'.
Any help would be nice.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 22, 2002 at 8:09 pm
Greg,
You can't alter a text column.
From the alter table help file...
<<<<<<<<<<<<<<<<<<<<<
ALTER COLUMN
Specifies that the given column is to be changed or altered. ALTER COLUMN is not allowed if the compatibility level is 65 or earlier. For more information, see sp_dbcmptlevel.
The altered column cannot be:
A column with a text, image, ntext, or timestamp data type.
>>>>>>>>>>>>>>>>>
It's a hassle but maybe you can create a new column - copy the data across - delete the old - then rename the new.
Cheers,
Mike.
September 23, 2002 at 8:17 am
Make sense now, but BOL under ALTER TABLE under COLLATE says this:
The COLLATE clause can be used to alter the collations only of columns of the char, varchar, text, nchar, nvarchar, and ntext data types. If not specified, the column is assigned the default collation of the database.
Don't you just love consistant documentation.
So I guess if you have a TEXT field in your database you will not be able to use the ALTER TABLE, ALTER column to convert from one collation to another.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply