SQL Server 2000 - How do I alter a column from text to ntext?

Question

I'm trying to do a ALTER TABLE Signatures ALTER COLUMN HTML ntext; but I get a Cannot alter column 'HTML' because it is 'text'.

How do I go about altering the column?

Answer 1

you can do it in two steps:

-- first alter from text to varchar
ALTER TABLE table_1 ALTER COLUMN [test] [varchar](max) NULL;
-- and finally to ntext
ALTER TABLE table_1 ALTER COLUMN [test] [ntext]  NULL;

Answer 2

Or you could rename HTML to HTMLOld and then create a new column HTML that is ntext. Then update the new column with the data from HTML old, then drop the HTMLOld column.

(Incidentally when you move away from SQL Server 2000, you need to start getting rid of these text and ntext columns as they are deprecated and will not be available in the next version of SQL Server.)