jaBowman


Hi All,

I have a table that has a column defined as:

[colname] [text] NOT NULL

and I want to change it to be

[colname] [ntext] NOT NULL

This table already has values in it (in this column).

I tried

ALTER TABLE [dbo][tablename] ALTER COLUMN [colname] [ntext] NOT NULL

But as the BOL describes I am not able to execute an ALTER COLUMN against a "text" column.

Any ideas




Re: HOW-TO Change "text" column to "ntext" that is NOT NULL

Arnie Rowland


If you are using SQL 2005, change it to nvarchar(max).

The text/ntext datatypes are being deprecated and must be converted to varchar(max)/nvarchar(max).







Re: HOW-TO Change "text" column to "ntext" that is NOT NULL

Manivannan.D.Sekaran

In SQL Server 2000, you can't modify the text column datatype.

But there is a workaround available..

Code Snippet

IF EXISTS(SELECT * FROM dbo.tablename)

SELECT othercolumns,Cast(colname as Ntext) as colname INTO dbo.Tmp_tablename FROM dbo.tablename TABLOCKX

go

DROP TABLE dbo.tablename

go

EXECUTE sp_rename N'dbo.Tmp_tablename', N'tablename', 'OBJECT'







Re: HOW-TO Change "text" column to "ntext" that is NOT NULL

jaBowman

Hi again Manivannan,

Thats an awesome solution but the tables I need to change "may" have constraints defined in it, or from it to some other tables. The effort to do what you suggest would be much more. There's an IDENTITY column in place in all of our tables and like I said any number of foreign keys associated to other tables, or other tables being associated to the one I'm trying to change. As well, some tables participate in views and have triggers associated with them. I wish it were as easy as your suggestion, unfortunately it isn't.





Re: HOW-TO Change "text" column to "ntext" that is NOT NULL

Umachandar Jayachandran - MS

Conversion from ASCII to Unicode or vice versa is usually not as simple as ALTER TABLE even if you can do it. Have you validated the data before the coversion process You can potentially lose or corrupt data due to the conversion. What about client applications or queries or stored procedures that manipulate the data There are many problems to consider and schema may be the easy part.
Anyway, if you are using SQL Server 2005 use the ALTER TABLE and convert to nvarchar(max). In SQL Server 2000, you can use the approach below:
1. add a new column of ntext type using ALTER TABLE
2. Run UPDATE to set the value of the ntext column from the text column. You might have to do some conversions if necessary or truncate data
3. drop the text column
4. alter ntext column to NOT NULL if necessary
5. rename the new column to same name as text column
6. modify SPs, queries, client code etc to handle the ntext column (this is the hard part)





Re: HOW-TO Change "text" column to "ntext" that is NOT NULL

jaBowman

This can't work, because the resultant column HAS to be NOT NULL.

In your process (Step 1), you can't add a column via the ALTER TABLE command that is defined to be NOT NULL without providing a default value. We don't want a default value defined for that column, so I cannot do this.

Although my only solution may be to create this new column with a default value, update/rename (as you suggest) then remove the default value afterwards, but I don't like having to do that and I want to know if there is an alternative.





Re: HOW-TO Change "text" column to "ntext" that is NOT NULL

Jason Callas

If you have access to SQL Management Studio (as opposed to Enterprise Manager or Query Analyzer) you can use the GUI to add the new column.

  • Right-click the table name and select Design from the context menu.
  • Change Data Type for column from text to ntext
  • Right-click design with and select Generate Change Script
  • Cut-and-paste resulting script from the dialog window to a query window.
  • Run it.

You could implement the change directly from the design window but depending on the amount of data you have it will probably timeout. (No timing out from the query window hence the cut-paste suggestion.)

You can read through the script but basically it creates a temporary table with the changed columns, copies over the data, deletes the old table, and renames the temp one. And yes...it is smart enough to handle and FK relationships you have.

Hope this helps.

- Jason





Re: HOW-TO Change "text" column to "ntext" that is NOT NULL

Umachandar Jayachandran - MS

It will work fine. Just add the column as NULL and you can change it to NOT NULL after updating the data.




Re: HOW-TO Change "text" column to "ntext" that is NOT NULL

jaBowman

Umachandar Jayachandran - MS wrote:
It will work fine. Just add the column as NULL and you can change it to NOT NULL after updating the data.

Sorry, I've tried this, it doesn't work with ntext columns.





Re: HOW-TO Change "text" column to "ntext" that is NOT NULL

jaBowman

Jason Callas wrote:

If you have access to SQL Management Studio (as opposed to Enterprise Manager or Query Analyzer) you can use the GUI to add the new column.

  • Right-click the table name and select Design from the context menu.
  • Change Data Type for column from text to ntext
  • Right-click design with and select Generate Change Script
  • Cut-and-paste resulting script from the dialog window to a query window.
  • Run it.

You could implement the change directly from the design window but depending on the amount of data you have it will probably timeout. (No timing out from the query window hence the cut-paste suggestion.)

You can read through the script but basically it creates a temporary table with the changed columns, copies over the data, deletes the old table, and renames the temp one. And yes...it is smart enough to handle and FK relationships you have.

Hope this helps.

- Jason

Wow, this is an awesome feature I didn't know existed, unfortunately what I am looking for is something generic that I can create some code for and loop over all possible "text" columns converting them to "ntext", in the database.

I think I will try adding the default value, then remove it afterwards, unless anyone has any other suggestions.





Re: HOW-TO Change "text" column to "ntext" that is NOT NULL

Jason Callas

You should not have to. I just tested this on my SQL 2000 system and was able to make the change with the column set to NOT NULL.

I even posted my sample script below. As you can see, the "new" column is marked as NOT NULL from the beginning.

Code Snippet

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_TestTable
(
Id int NOT NULL IDENTITY (1, 1),
Data ntext NOT NULL
) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_TestTable ON
GO
IF EXISTS(SELECT * FROM dbo.TestTable)
EXEC('INSERT INTO dbo.Tmp_TestTable (Id, Data)
SELECT Id, CONVERT(ntext, Data) FROM dbo.TestTable WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_TestTable OFF
GO
DROP TABLE dbo.TestTable
GO
EXECUTE sp_rename N'dbo.Tmp_TestTable', N'TestTable', 'OBJECT'
GO
COMMIT





Re: HOW-TO Change "text" column to "ntext" that is NOT NULL

Umachandar Jayachandran - MS

Below works fine in SQL Server 2005:
Code Snippet
use tempdb;
go
create table t ( t text not null );
alter table t add nt ntext null
alter table t alter column nt ntext not null;

drop table t;

Which version of SQL Server are you using The ALTER of text/ntext might have restrictions in SQL Server 2000. Else you have to go the default route or add column as NULL and add a CHECK constraint like (col is not null).





Re: HOW-TO Change "text" column to "ntext" that is NOT NULL

jaBowman

Jason,

My problem with doing this is that (my code) would have to figure out all of the possible constraints on the table it is recreating at execution time. My code has to work on any table in my database that has a "text" column. Your sample code is very specific to the table you attempted this on, that you generated the script from. I need (would like) a generic solution that I can execute against any table (with any possible constraints, views, triggers, etc), so any solution that tries to recreate the table, will be extremely tricky to implement because I don't know what constraints/views/triggers/etc may be associated with it.

Like I said I'm going to try to do the following, and I'll report back with my results:

1 - Rename existing column

2 - Add new column, set to be NOT NULL but will add a specifically named default constraint of an empty string.

3 - Update the new column with the old_columns' data

4 - Drop the old column

5 - Remove default constraint.





Re: HOW-TO Change "text" column to "ntext" that is NOT NULL

jaBowman

Umachandar Jayachandran - MS wrote:
Below works fine in SQL Server 2005:
Code Snippet
use tempdb;
go
create table t ( t text not null );
alter table t add nt ntext null
alter table t alter column nt ntext not null;

drop table t;

Which version of SQL Server are you using The ALTER of text/ntext might have restrictions in SQL Server 2000. Else you have to go the default route or add column as NULL and add a CHECK constraint like (col is not null).

I'm trying to get this to work in SQL Server 2000.





Re: HOW-TO Change "text" column to "ntext" that is NOT NULL

Arnie Rowland

Umachandar,

Just a note to clarify for the OP that with for a table with existing data, the code below will fail UNLESS there is EITHER

a UPDATE statement providing values for column [nt] after the first ALTER TABLE statement,

OR,

a DEFAULT value supplied with the second ALTER TABLE statement.

Umachandar Jayachandran - MS wrote:

...
Code Snippet
use tempdb;
go
create table t ( t text not null );
alter table t add nt ntext null
alter table t alter column nt ntext not null;

drop table t;
...