Convert ntext to varchar in sql

thanks but i need to preserve the field name.

Why not copy it to a temp field in the format you want.
drop the column with alter table,
add the new column with the old name.
copy the data back
drop the temp column?

At the semantic level (not filling in all the syntax) it would look like this:

1. Alter table add temp_column as varchar(8000)
2. update mytable set temp_column=cast (left(8000,Old_column) as varchar)     <- this safeguards against oversize data by truncating

3. alter table drop Old_column
4. alter table add Old_column  as varchar (8000)
5 update mytable set old_column=temp_column
6. alter table drop temp_column.

Do you need to change the data type of the ntext column? If so, try something like this:

ALTER TABLE table_name ALTER COLUMN column_name varchar(255)

That would work as long as the Ntext data doesn't violate the size restrictions of varchar.

Fast query to find it would be

Select max len(column) from mytable

this should return the longest data element in your column.

I did test this and it does work.  It's very fast I might add.  I ran it on a table we have with 24000 rows.

Thanks guys.  ntjock was going to go this router before rollback's post confused me! I should have mentioned I posted as the alter described by rollback will not work, in sql 2000 it gives :

Cannot alter column column_name because it is 'ntext'.

What am I missing?

Hmmm...

If all else fails resort to the GUI.

If this is a one time task then you could use EM.

go in, add the new column using "design table" in EM.

Test your column length to see if you have a data issue.
If you don't have a data issue, copy the data to the new column.

Then go back to EM and drop the offending column using design table.
Delete, save.  Add new column with same name.  Save.

Copy data back.

go back to "design" and drop temp column.

Accomplishes the same thing with some extra mouse work and not having to figure out the intracies of alter table statements.

I freely admit I'm a wimp when it comes to alter table.  I'm afraid I'll type the wrong thing and zap data.  So I go through the gui. modify, check, and then save.

And on certain tables I do a Select Into to make a backup first.

I call it my "nights and weekends free plan".

Ok, my bad, I apologize. ntext columns cannot be altered.

From MSSQL manual:
...
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 column with a text, image, ntext, or timestamp data type.
...

Thanks mate. I would recommend begin transaction & rollback transaction.!
My nights and weekend free plan distrusts gui's!
still playing with it but step 2
2. update mytable set temp_column=cast (left(8000,Old_column) as varchar)  
is giving
Operand type clash: ntext is incompatible with int
at the moment....

could not getting anything else to work..thanks...