By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,262 Members | 1,161 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,262 IT Pros & Developers. It's quick & easy.

Non-Unicode to Unicode Data conversion

P: n/a
Hi all, we are now planning to upgrade our application from a
non-unicode version to a unicode version. The application's backend is
a SQL Server 2000 SP3.

The concern is, existing business data are stored using collation
"Chinese_PRC_CI_AS", i.e. Simplified Chinese. So I thought we need to
extract these data out to the new SQL Server which is using Unicode (I
assume it means converting them to nchar, nvarchar type of fields for I
don't enough information from the application side, or is there a
general unicode collation that will make even char and varchar types to
store data as Unicode?).

The problem is what's the best and most efficient way to do this data
conversion?
bcp? DTS? or others?

thanks a lot

Jul 23 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
New MSSQL DBA (bo*******@gmail.com) writes:
Hi all, we are now planning to upgrade our application from a
non-unicode version to a unicode version. The application's backend is
a SQL Server 2000 SP3.

The concern is, existing business data are stored using collation
"Chinese_PRC_CI_AS", i.e. Simplified Chinese. So I thought we need to
extract these data out to the new SQL Server which is using Unicode (I
assume it means converting them to nchar, nvarchar type of fields for I
don't enough information from the application side, or is there a
general unicode collation that will make even char and varchar types to
store data as Unicode?).
You will have to move to nchar/nvarchar.
The problem is what's the best and most efficient way to do this data
conversion?
bcp? DTS? or others?


One idea would be to create a new database on the same server, with
the (var)char columns changed to n(var)char columns, and then insert
data over. In this case you will get a conversion from the multi-byte
character set you use today. You would then move that database to the
new server with detach/attach or backup/restore.

You would not create indexes, constraints and triggers in the new
database, until you have copied the data.

Using BCP meand that you have to bounce over disk. Then again, bulk-
load is faster so it could still be faster. Here I cannot really say
that you will get a conversion, although I believe that you would.
(I have never converted Chinese text from double-byte to Unicode, so
I don't really know what works and what does not.)

As for DTS, I don't know DTS at all, so I can't say whether it's good or
not.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2

P: n/a
New MSSQL DBA (bo*******@gmail.com) writes:
Hi all, we are now planning to upgrade our application from a
non-unicode version to a unicode version. The application's backend is
a SQL Server 2000 SP3.

The concern is, existing business data are stored using collation
"Chinese_PRC_CI_AS", i.e. Simplified Chinese. So I thought we need to
extract these data out to the new SQL Server which is using Unicode (I
assume it means converting them to nchar, nvarchar type of fields for I
don't enough information from the application side, or is there a
general unicode collation that will make even char and varchar types to
store data as Unicode?).
You will have to move to nchar/nvarchar.
The problem is what's the best and most efficient way to do this data
conversion?
bcp? DTS? or others?


One idea would be to create a new database on the same server, with
the (var)char columns changed to n(var)char columns, and then insert
data over. In this case you will get a conversion from the multi-byte
character set you use today. You would then move that database to the
new server with detach/attach or backup/restore.

You would not create indexes, constraints and triggers in the new
database, until you have copied the data.

Using BCP meand that you have to bounce over disk. Then again, bulk-
load is faster so it could still be faster. Here I cannot really say
that you will get a conversion, although I believe that you would.
(I have never converted Chinese text from double-byte to Unicode, so
I don't really know what works and what does not.)

As for DTS, I don't know DTS at all, so I can't say whether it's good or
not.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3

P: n/a
Thanks for your help.

I am wondering I can do an in-place conversion by altering the char,
varchar, text columns of the original non-unicode DB to nchar, nvarchar
and ntext columns using the ALTER TABLE ... ALTER COLUMN commands.

I've done a bit of testing and it seems working. Just wondering
whether there are any catch of doing so. (of couse, will backup
everything before changes).

Jul 23 '05 #4

P: n/a
Thanks for your help.

I am wondering I can do an in-place conversion by altering the char,
varchar, text columns of the original non-unicode DB to nchar, nvarchar
and ntext columns using the ALTER TABLE ... ALTER COLUMN commands.

I've done a bit of testing and it seems working. Just wondering
whether there are any catch of doing so. (of couse, will backup
everything before changes).

Jul 23 '05 #5

P: n/a
New MSSQL DBA (bo*******@gmail.com) writes:
I am wondering I can do an in-place conversion by altering the char,
varchar, text columns of the original non-unicode DB to nchar, nvarchar
and ntext columns using the ALTER TABLE ... ALTER COLUMN commands.

I've done a bit of testing and it seems working. Just wondering
whether there are any catch of doing so. (of couse, will backup
everything before changes).


I can't see any problems with this.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #6

P: n/a
New MSSQL DBA (bo*******@gmail.com) writes:
I am wondering I can do an in-place conversion by altering the char,
varchar, text columns of the original non-unicode DB to nchar, nvarchar
and ntext columns using the ALTER TABLE ... ALTER COLUMN commands.

I've done a bit of testing and it seems working. Just wondering
whether there are any catch of doing so. (of couse, will backup
everything before changes).


I can't see any problems with this.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.