Welcome to Vortx Community Forum, where you can ask questions and receive answers from the staff at Vortx and other members of the community.

If you had a user account on our previous forums website, you will need to register a new account here.

Learn more about...

AspDotNetStorefront
DotFeed

Best way to convert all ntext fields to nvarchar(MAX)?

I was running into several issues trying to work with the depreciated ntext field in ASPDNSF. I therefore decided to convert it all over to nvarchar(MAX). I've used the following script. I can't think of a better way. Does anyone else have ideas? (Partially from http://stackoverflow.com/questions/18789810/how-can-i-easily-convert-all-ntext-fields-to-nvarcharmax-in-sql-query)

 

ALTER TABLE [dbo].[Orders] DROP CONSTRAINT [DF_Orders_RecurringSubscriptionCommand]
GO
 
ALTER TABLE [dbo].[Orders] DROP CONSTRAINT [DF_Orders_RecurringSubscriptionResult]
GO
 
ALTER TABLE [dbo].[Orders] DROP CONSTRAINT [DF_Orders_BuySafeCommand]
GO
 
ALTER TABLE [dbo].[Orders] DROP CONSTRAINT [DF_Orders_BuySafeResult]
GO
 
DECLARE TableCursor CURSOR FAST_FORWARD 
FOR
    SELECT 
        t.Name,
        c.name,
        c.is_nullable, 
        typ.user_type_id
    FROM 
        sys.columns c 
    INNER JOIN 
        sys.tables t ON c.object_id = t.object_id
    INNER JOIN 
        sys.types typ ON c.system_type_id = typ.system_type_id
    WHERE   
        typ.name IN ('text', 'ntext')    -- user_type_id: text = 35, ntext = 99 
 
DECLARE @TableName sysname, @ColumnName sysname, @IsNullable BIT, @TypeID INT
 
OPEN TableCursor
 
FETCH NEXT FROM TableCursor INTO @TableName, @ColumnName, @IsNullable, @TypeID
 
WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @Stmt NVARCHAR(999)
 
    SET @Stmt = 'ALTER TABLE dbo.[' + @TableName + '] ALTER COLUMN [' + @ColumnName + '] ' + 
              CASE @TypeID 
                WHEN 35 THEN ' VARCHAR(MAX) '
                WHEN 99 THEN ' NVARCHAR(MAX) '
              END  + 
                CASE WHEN @IsNullable = 1 THEN 'NULL' ELSE 'NOT NULL' END
 
    PRINT @Stmt
    EXEC (@Stmt)
 
SET @Stmt = 'UPDATE dbo.[' + @TableName + '] SET [' + @ColumnName + '] = [' + @ColumnName + ']'
 
    PRINT @Stmt
    EXEC (@Stmt)
 
    FETCH NEXT FROM TableCursor INTO @TableName, @ColumnName, @IsNullable, @TypeID
END
 
CLOSE TableCursor
DEALLOCATE TableCursor
GO
 
ALTER TABLE [dbo].[Orders] ADD  CONSTRAINT [DF_Orders_BuySafeResult]  DEFAULT ('') FOR [BuySafeResult]
GO
 
ALTER TABLE [dbo].[Orders] ADD  CONSTRAINT [DF_Orders_BuySafeCommand]  DEFAULT ('') FOR [BuySafeCommand]
GO
 
ALTER TABLE [dbo].[Orders] ADD  CONSTRAINT [DF_Orders_RecurringSubscriptionResult]  DEFAULT ('') FOR [RecurringSubscriptionResult]
GO
 
ALTER TABLE [dbo].[Orders] ADD  CONSTRAINT [DF_Orders_RecurringSubscriptionCommand]  DEFAULT ('') FOR [RecurringSubscriptionCommand]
GO
 
asked Jan 10, 2014 in MultiStore by cjbarth (110 points)

2 Answers

0 votes
alter table Product add add_flag char(1)
alter table product add velocity int

alter table productvariant add add_flag char(1)
alter table productvariant add uom varchar(10)
alter table productvariant add item_id varchar(40)


alter table category alter column summary nvarchar(2000);
alter table category alter column Description nvarchar(2000);

alter table product alter column summary nvarchar(max);
alter table product alter column Description nvarchar(max);
alter table product alter column sekeywords nvarchar(2000);
alter table product alter column sedescription nvarchar(2000);
alter table product alter column setitle nvarchar(2000);
alter table product alter column senoscript nvarchar(2000);
alter table product alter column sealttext nvarchar(2000);
alter table product alter column sizeoptionprompt nvarchar(2000);
alter table product alter column coloroptionprompt nvarchar(2000);
alter table product alter column  textoptionprompt nvarchar(2000);
alter table product alter column  notes nvarchar(2000);
alter table product alter column  relatedproducts nvarchar(2000);
alter table product alter column  upsellproducts nvarchar(2000);
alter table product alter column  relateddocuments nvarchar(2000);
alter table product alter column requiresproducts  nvarchar(2000);
alter table product alter column  extensiondata nvarchar(2000);
alter table product alter column  extensiondata2 nvarchar(2000);
alter table product alter column  extensiondata3 nvarchar(2000);
alter table product alter column  extensiondata4 nvarchar(2000);
alter table product alter column  extensiondata5 nvarchar(2000);
alter table product alter column imagefilenameoverride  nvarchar(2000);
alter table product alter column MiscText  nvarchar(max);


alter table productVariant alter column description   nvarchar(max);
alter table productVariant alter column  sekeywords  nvarchar(2000);
alter table productVariant alter column  sedescription  nvarchar(max);
alter table productVariant alter column  sealttext  nvarchar(2000);
alter table productVariant alter column  colors  nvarchar(2000);
alter table productVariant alter column  colorskumodifiers  nvarchar(2000);
alter table productVariant alter column  siZes  nvarchar(2000);
alter table productVariant alter column  sizeskumodifiers  nvarchar(2000);
alter table productVariant alter column  froogledescription  nvarchar(2000);
alter table productVariant alter column  notes  nvarchar(2000);

alter table productVariant alter column  downloadlocation  nvarchar(2000);
alter table productVariant alter column extensiondata   nvarchar(2000);
alter table productVariant alter column extensiondata2   nvarchar(2000);
alter table productVariant alter column extensiondata3   nvarchar(2000);
alter table productVariant alter column extensiondata4   nvarchar(2000);
alter table productVariant alter column extensiondata5   nvarchar(2000);
alter table productVariant alter column imagefilenameoverride   nvarchar(2000);

alter table Section alter column summary nvarchar(2000);
alter table Section alter column Description nvarchar(2000);
alter table Section alter column sekeywords nvarchar(2000);
alter table Section alter column sedescription nvarchar(2000);
alter table Section alter column DisplayPrefix nvarchar(2000);
alter table Section alter column setitle nvarchar(2000);
alter table Section alter column senoscript nvarchar(2000);
alter table Section alter column sealttext nvarchar(2000);
alter table Section alter column RelatedDocuments nvarchar(2000);
alter table Section alter column  extensiondata nvarchar(2000);
alter table Section alter column imagefilenameoverride  nvarchar(2000);






alter table category alter column sekeywords nvarchar(2000);
alter table category alter column sedescription nvarchar(2000);

alter table category alter column setitle nvarchar(2000);
alter table category alter column senoscript nvarchar(2000);
alter table category alter column sealttext nvarchar(2000);
alter table category alter column RelatedDocuments nvarchar(2000);
alter table category alter column  extensiondata nvarchar(2000);
alter table category alter column imagefilenameoverride  nvarchar(2000);
alter table manufacturer alter column summary nvarchar(2000);
alter table manufacturer alter column Description nvarchar(2000);
alter table manufacturer alter column sekeywords nvarchar(2000);
alter table manufacturer alter column sedescription nvarchar(2000);

alter table manufacturer alter column setitle nvarchar(2000);
alter table manufacturer alter column senoscript nvarchar(2000);
alter table manufacturer alter column sealttext nvarchar(2000);
alter table manufacturer alter column RelatedDocuments nvarchar(2000);
alter table manufacturer alter column  extensiondata nvarchar(2000);
alter table manufacturer alter column imagefilenameoverride  nvarchar(2000);
alter table manufacturer alter column ExtensionData  nvarchar(2000);

alter table orders_shoppingcart alter column orderedproductname nvarchar(2000);
alter table orders_shoppingcart alter column orderedproductvariantname nvarchar(2000);
alter table orders_shoppingcart alter column downloadlocation nvarchar(2000);
alter table orders_shoppingcart alter column textoption nvarchar(2000);
alter table orders_shoppingcart alter column shippingdetail nvarchar(2000);
alter table orders_shoppingcart alter column shippingmethod nvarchar(2000);
alter table orders_shoppingcart alter column notes nvarchar(2000);
alter table orders_shoppingcart alter column extensiondata nvarchar(2000);
alter table orders_shoppingcart alter column sizeoptionprompt nvarchar(2000);
alter table orders_shoppingcart alter column  coloroptionprompt nvarchar(2000);
alter table orders_shoppingcart alter column  textoptionprompt nvarchar(2000);

i actually run this on every new site

 

 

answered Jan 12, 2014 by Mikecali (1,525 points)
0 votes
Check out this article on converting ntext to nvarcharmax...

 

http://geekswithblogs.net/johnsPerfBlog/archive/2008/04/16/ntext-vs-nvarcharmax-in-sql-2005.aspx
answered Feb 4, 2014 by mal247 (130 points)
...