Umachandar Jayachandran - MS
Conversion from ASCII to Unicode or vice versa is usually not as simple as ALTER TABLE even if you can do it. Have you validated the data before the coversion process You can potentially lose or corrupt data due to the conversion. What about client applications or queries or stored procedures that manipulate the data There are many problems to consider and schema may be the easy part.
Anyway, if you are using SQL Server 2005 use the ALTER TABLE and convert to nvarchar(max). In SQL Server 2000, you can use the approach below:
1. add a new column of ntext type using ALTER TABLE
2. Run UPDATE to set the value of the ntext column from the text column. You might have to do some conversions if necessary or truncate data
3. drop the text column
4. alter ntext column to NOT NULL if necessary
5. rename the new column to same name as text column
6. modify SPs, queries, client code etc to handle the ntext column (this is the hard part)