changing field data type - SQL

Customer Support Database Support
1

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:

[INDENT]Cannot alter column ‘Message’ because it is ‘text’[/INDENT]

with this SQL:

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

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

2

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

3

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

4

Hi,

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

Thanx
Yogesh

5

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

6

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