?
Solved

Guidance on Unicode conversion

Posted on 2006-04-24
7
Medium Priority
?
281 Views
Last Modified: 2012-05-05
I'm upgrading an enterprise app database with 130 tables to support unicode. As far as I know this means turning all varchar to nvarchar, text to ntext and char to nchar.

I wrote the following script...

select
'alter table ' + b.name + ' alter column ' + a.name + ' nvarchar(' + convert(varchar(40), a.length) + ')' +
case a.isnullable when 1 then ' NULL' else ' NOT NULL' END As AlterStmt,
b.name, a.name, a.xtype, a.length, a.* from syscolumns as a inner join sysobjects as b on a.id = b.id
where a.xtype = 167
and b.type = 'U'
order by b.name, a.name

Thats giving me alter statements for every varchar field, but when I try to run it, it fails because indexes exist. There probably are also one or two char fields which form keys although 99% will be pk'd on an incremental int field

Question is: is there an easy way of getting through this without dropping all the indexes etc ? Otherwise I suppose I have to go the generate sql script route - drop the indexes, run the alter tables, and then recreate the indexes.

I am looking for the least possible impact solution since the current database is live at over 100 customer sites and I must minimise risk during upgrades



0
Comment
Question by:plq
  • 3
  • 2
  • 2
7 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16526252
The problem with your query is
1.You cant alter a Text column to ntext, you need to  create the column and populate the data for this
2. You are altering the table dtProperties'
0
 
LVL 8

Author Comment

by:plq
ID: 16526717
thanks, but my Question is at the bottom: see "Question is:" above

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16526898
plq,
>  is there an easy way of getting through this without dropping all the
> indexes etc ?

NO otherway You should drop and create the index
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 1000 total points
ID: 16526913
there is no way to alter an index other than dropping and recreating, in sql server 2000
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16532054
Depends upon the number of tables you have?
You can do this in Enterprise Manager by going into the design of the table it will automatically drop and recreate the indexes for you.
0
 
LVL 8

Author Comment

by:plq
ID: 16532214
120 tables and installed at over 100 customer sites, each one will need to be upgraded. So I'm hoping for some silver bullet thats going to allow me to achieve this reliably !! Sounds like I just need to generate sql scripts, drop the indexes, alter tables, recreate ntext, and then recreate indexes
0
 
LVL 28

Accepted Solution

by:
imran_fast earned 1000 total points
ID: 16532786
You can create a change script for each table once and then deploy those change scripts to each client site.
When you made any change in the design mode in the enterprise manager the 3rd icon (script changes is highlighted) click on that and save it for the changes.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question