changing field data type - SQL

[snooper]

i'm using EMS MS SQL manager to look at a DB on the site.
i'm trying to change a field from "text" to "ntext" - but i'm getting an error:

Cannot alter column 'Message' because it is 'text'​

with this SQL:

ALTER TABLE [dbo].[tblThread]
ALTER COLUMN [Message] ntext COLLATE SQL_Latin1_General_CP1_CI_AS
GO​

does anyone know why? i have confirmed that there is no data (records) at all in that table.

is this something that support can do with out errors? how can i overcome this?

thanks!

PS: i also tried to login thru th CP to the MSSQl manager - but got a "page cannot be shown" error at: http://YYYY:[email protected]/MSSQL/app/connect.aspx

 

[megan]

can't help with why the type won't convert, but to access the mssql manager try replacing the url with http://torahmitzion.org/MSSQL/app/connect.aspx - you will be prompted for your hosting account login and password.

hth ~megan

 

[yogesh]

create a ticked for that and provide information such as database name, username and password. For data manipulation always use MSSQL Manager by CP.

Thanx
Yogesh

 

[yogesh]

Hi,

I connect to your database using EMS MS SQL manager, please specify which table you want to modify.

Thanx
Yogesh

 

[dannyc]

This is a SQL Server restriction; you can not use an alter statement on a text (or image, ntext, or timestamp) field. However, since you don't have any data in the table, you can just drop the column and re-add a new one with the proper type, like this:

ALTER TABLE dbo.tblThread DROP COLUMN Message
ALTER TABLE dbo.tblThread ADD Message NTEXT COLLATE  SQL_Latin1_General_CP1_CI_AS

 

[snooper]

Thanks Megan and dannyc - both your answers helped me solve the issue!