ALTER COLUMN on a text or ntext field

Hi,
I'd like to run the following command:
ALTER TABLE cal_respurpose ALTER COLUMN [purpose] varchar(255)
but it falls over because the current [purpose] column is 'text': -
Server: Msg 4928, Level 16, State 1, Line 1
Cannot alter column 'purpose' because it is 'text'.
I can change it through design view in Enterprise manager, after clicking ok on the warning message, but I need to find a way to override this error in Script.
Is there a way I can overide the fact that it is a text column and change it to varchar?
Thanks in advance!
Paul
Got answer from Aaron Bertrand [SQL Server MVP] on another newsgroup.
EXEC sp_rename 'cal_respurpose.purpose', 'purpose_old', 'COLUMN'
ALTER TABLE cal_respurpose ADD purpose VARCHAR(255)
UPDATE cal_respurpose SET purpose = SUBSTRING(purpose_old, 1, 255)
ALTER TABLE cal_respurpose DROP COLUMN purpose_old
Cheers!
"Paul B" <paul.bunting@.archsoftnet.com> wrote in message news:%23J%23SJCsvFHA.1996@.TK2MSFTNGP10.phx.gbl...
Hi,
I'd like to run the following command:
ALTER TABLE cal_respurpose ALTER COLUMN [purpose] varchar(255)
but it falls over because the current [purpose] column is 'text': -
Server: Msg 4928, Level 16, State 1, Line 1
Cannot alter column 'purpose' because it is 'text'.
I can change it through design view in Enterprise manager, after clicking ok on the warning message, but I need to find a way to override this error in Script.
Is there a way I can overide the fact that it is a text column and change it to varchar?
Thanks in advance!
Paul
|||Cool, but this code will add the column to the end of the table, the column "purpose" will be the last one in "select * from cal_respurpose". this could be risky if the software or the store procedures performs an insert based on the columns indices.
so what i would suggest is to copy the table to a new table ( with new stucture ) after backing up the table and renaming the new one to the original table name.
I don't know if there is a way to preserve the columns order.
Faris

Quote:

Originally Posted by Paul B

Got answer from Aaron Bertrand [SQL Server MVP] on another newsgroup.
EXEC sp_rename 'cal_respurpose.purpose', 'purpose_old', 'COLUMN'
ALTER TABLE cal_respurpose ADD purpose VARCHAR(255)
UPDATE cal_respurpose SET purpose = SUBSTRING(purpose_old, 1, 255)
ALTER TABLE cal_respurpose DROP COLUMN purpose_old
Cheers!
"Paul B" <paul.bunting@.archsoftnet.com> wrote in message news:%23J%23SJCsvFHA.1996@.TK2MSFTNGP10.phx.gbl...
Hi,
I'd like to run the following command:
ALTER TABLE cal_respurpose ALTER COLUMN [purpose] varchar(255)
but it falls over because the current [purpose] column is 'text': -
Server: Msg 4928, Level 16, State 1, Line 1
Cannot alter column 'purpose' because it is 'text'.
I can change it through design view in Enterprise manager, after clicking ok on the warning message, but I need to find a way to override this error in Script.
Is there a way I can overide the fact that it is a text column and change it to varchar?
Thanks in advance!
Paul