How to the datatype from text to nText

Question

Post reply

How to the datatype from text to nText

Jerry Cheung

SSC Veteran

Points: 221
More actions
February 23, 2004 at 8:17 pm

#359118

I want to change the datatype from 'text' to 'nText' and the sql is followed:
ALTER TABLE SOHEAD
ALTER COLUMN SOHTEXT NTEXT
but it has the error message:
Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF_SOHEAD_SOHTEXT' is dependent on column 'SOHTEXT'.
Server: Msg 4928, Level 16, State 1, Line 1
Cannot alter column 'SOHTEXT' because it is 'text'.
WHY???

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

TriggerMe SSChampion Points: 11752 More actions · Answer 1

You can't ALTER a Text Column!

You will haveto create a new Column of DataType NText and copy the data in that new Column.

Kindest Regards,

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

If you are sure that you don't have data longer than 8,000 characters you can use a somewhat messy workaround. Open the table in EM and change the data type from text to varchar(8000) and save. Now change the data type to ntext. Of course, you can also do this in QA, but then you'll have to find out such a cryptic name like DF__text_samp__mytex__15702A09.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Mehman SSC Veteran Points: 271 More actions · Answer 3

ALTER TABLE SOHEAD drop constraint DF_SOHEAD_SOHTEXT

go

ALTER TABLE SOHEAD

ALTER COLUMN SOHTEXT NTEXT

Jerry Cheung SSC Veteran Points: 221 More actions · Answer 4

THANKS

However, I have to change the fields from text to nText for the whole database.

Any help ?

Thanks!

Jerry Cheung SSC Veteran Points: 221 More actions · Answer 5

I think I should do in this way.

any opinion?

SET QUOTED_IDENTIFIER OFF

GO

SET NOCOUNT OFF

DECLARE @COLUMN_NAME AS NVARCHAR(50),

@TABLE_NAME AS NVARCHAR (50),

@Acommand AS NVARCHAR(90),

@Bcommand AS NVARCHAR(90),

@Ccommand AS NVARCHAR(90),

@Dcommand AS NVARCHAR(90),

@Ecommand AS NVARCHAR(90),

@COLUMN_NAME_OLD AS NVARCHAR(50),

@PREFIX AS NVARCHAR (2),

@CONSTRAINT_NAME AS NVARCHAR (80)

SELECT @PREFIX = '11'

DECLARE TABLENAME_cursor CURSOR FOR

SELECT sysobjects.NAME, syscolumns.NAME, sysobjects_1.NAME

FROM sysconstraints INNER JOIN

syscolumns ON sysconstraints.id = syscolumns.id AND

sysconstraints.colid = syscolumns.colid INNER JOIN

sysobjects INNER JOIN

sysobjects sysobjects_1 ON sysobjects.id = sysobjects_1.parent_obj ON

syscolumns.id = sysobjects.id AND sysconstraints.constid = sysobjects_1.id

where syscolumns.XTYPE = 35 -- AND sysobjects.NAME='RWODBF'

ORDER BY sysobjects.NAME

OPEN TABLENAME_cursor

FETCH NEXT FROM TABLENAME_cursor INTO @TABLE_NAME, @COLUMN_NAME,@CONSTRAINT_NAME

WHILE @@FETCH_STATUS =0

BEGIN

SELECT @COLUMN_NAME_OLD = @COLUMN_NAME + @PREFIX

--PRINT @COLUMN_NAME_OLD

set @Acommand = "ALTER TABLE " + @TABLE_NAME + " ADD " + @COLUMN_NAME_OLD +" ntext"

PRINT @Acommand

execute (@Acommand)

set @Bcommand = "update " + @TABLE_NAME + " SET " + @COLUMN_NAME_OLD + " = " + @COLUMN_NAME

PRINT @Bcommand

execute (@Bcommand)

set @Ecommand = "ALTER TABLE " + @TABLE_NAME + " DROP CONSTRAINT " + @CONSTRAINT_NAME

PRINT @Ecommand

execute (@Ecommand)

set @Ccommand = "ALTER TABLE " + @TABLE_NAME + " DROP COLUMN " + @COLUMN_NAME

PRINT @Ccommand

execute (@Ccommand)

SET @Dcommand = "EXEC SP_RENAME '" + @TABLE_NAME + ".[" + @COLUMN_NAME_OLD + "]', '" + @COLUMN_NAME + "', 'COLUMN'"

PRINT @Dcommand

execute (@Dcommand)

FETCH NEXT FROM TABLENAME_cursor INTO @TABLE_NAME, @COLUMN_NAME,@CONSTRAINT_NAME

END

CLOSE TABLENAME_cursor

DEALLOCATE TABLENAME_cursor

GO