As there is no direct way of converting a TEXT or NTEXT field to VARCHAR, it is necessary to rename your existing field, create a new field of the required size and then copy the existing data across, converting it, and then dropping the old column.
The following sample shows how to achieve this: –
EXECsp_rename ‘table_name.original_field_name’, ‘temp_field_name’, ‘COLUMN’
ALTER TABLE [table_name] ADD [original_field_name] VARCHAR(1000)
UPDATE [table_name] SET [original_field_name] = SUBSTRING([temp_field_name], 1, 1000)
ALTER TABLE [table_name] DROP COLUMN [temp_field_name]