How to alter a 'text' column to 'ntext' ?

Hi all,
how to do that with SQL7 or 2000 by Transact-SQL '
If I read the SQL2000 doc on 'Alter Table' :[vbcol=seagreen]
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.. A column with a text, image, ntext, or timestamp data type.
<<<<<<<<<<<<<<<<<<<<<<<

Lilian.Hi,
You cannot alter the text column directly.
2 Approaches to do this
--
I.
1.Create an ntext column,
2.update the text column's data to the ntext column,
3.drop the text column (Alter table drop column <column_name>
4.rename the ntext column to the prior text column's name (
5.sp_rename 'table_name.column_name',old_col_name,'column')
II.
1. You have export the data out (BCP OUT/DTS)
2. Take the script of table and dependant objects
3. Drop the table
4. In create tabl script change TEXT datatype to NTEXT
5. Craete the table.
6. BCP IN/ DTS to Import data
7. Create the dependant objects
You can also try the below, which requires less downtime
Thanks
Hari
MCDBA
"Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
news:u3iKxbGVEHA.4092@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> how to do that with SQL7 or 2000 by Transact-SQL '
> If I read the SQL2000 doc on 'Alter Table' :
> 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.. A column with a text, image, ntext, or timestamp data type.
> <<<<<<<<<<<<<<<<<<<<<<<
>
>
> Lilian.
>|||Thanks )
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23HoJXlGVEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Hi,
> You cannot alter the text column directly.
> 2 Approaches to do this
> --
> I.
> 1.Create an ntext column,
> 2.update the text column's data to the ntext column,
> 3.drop the text column (Alter table drop column <column_name>
> 4.rename the ntext column to the prior text column's name (
> 5.sp_rename 'table_name.column_name',old_col_name,'column')
>
> II.
> 1. You have export the data out (BCP OUT/DTS)
> 2. Take the script of table and dependant objects
> 3. Drop the table
> 4. In create tabl script change TEXT datatype to NTEXT
> 5. Craete the table.
> 6. BCP IN/ DTS to Import data
> 7. Create the dependant objects
>
> You can also try the below, which requires less downtime
> --
> Thanks
> Hari
> MCDBA
> "Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
> news:u3iKxbGVEHA.4092@.TK2MSFTNGP11.phx.gbl...
COLUMN[vbcol=seagreen]
> is
>sql