SQL Server 2000 - How to change a column from text in ntext?

Question

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

How do I change a column?

Answer 1

Or you can rename the HTML to HTMLOld and then create a new HTML code for the column, which is ntext. Then update the new column with the data from the old HTML, then release the HTMLOld column.

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

Answer 2

You can do this 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 3

1) Create a new ntext data type column in your table 2) Run the update statement to copy from html to the new ntext column 3) Clear the html column 4) Rename the new column to html (if necessary)

Answer 4

You can not. You need to create a new table (including permissions, triggers, etc.), copy the data and delete the old table.