Non-Unicode to Unicode Data conversion

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 lotNew MSSQL DBA (boscong88@.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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||New MSSQL DBA (boscong88@.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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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).|||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).|||New MSSQL DBA (boscong88@.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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||New MSSQL DBA (boscong88@.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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

non unicode

Hi,
Is DT_STR Unicode? From SSIS I am using a data conversion component
and defining the data type as DT_STR and saving the output in excel
file. I am getting an error that 'can not convert from Unicode to non-
unicode'. I thought I have already taken care of that in data
conversion.
Could you explain exactly which data conversion you prepared? The DT_STR was
specified for the input field or for the output? In this last case, which is
the input field data type?
Gilberto Zampatti
"SB" wrote:

> Hi,
> Is DT_STR Unicode? From SSIS I am using a data conversion component
> and defining the data type as DT_STR and saving the output in excel
> file. I am getting an error that 'can not convert from Unicode to non-
> unicode'. I thought I have already taken care of that in data
> conversion.
>
|||On Apr 20, 2:10 pm, Gilberto Zampatti
<GilbertoZampa...@.discussions.microsoft.com> wrote:
> Could you explain exactly which data conversion you prepared? The DT_STR was
> specified for the input field or for the output? In this last case, which is
> the input field data type?
> Gilberto Zampatti
>
> "SB" wrote:
> - Show quoted text -
Hi,
DT_STR should be an input field because I am getting data from a table
using ole db source.
|||OK; supposing that input field is actually non-unicode (DT_STR could be the
right data type), verify the transformation task to be sure about the output
data type. SQL 2005 uses UNICODE by default, and you should explicitly
declare non unicode data types.
If you correct the input field definition specifying Unicode (DT_WSTR), you
should avoid such error message.
The fact that you are accessing input table via OLE DB does'nt change the
fact that QSL 2005 uses Unicode by default.
Gilberto Zampatti
"SB" wrote:

> On Apr 20, 2:10 pm, Gilberto Zampatti
> <GilbertoZampa...@.discussions.microsoft.com> wrote:
> Hi,
> DT_STR should be an input field because I am getting data from a table
> using ole db source.
>
|||On Apr 20, 3:28 pm, Gilberto Zampatti
<GilbertoZampa...@.discussions.microsoft.com> wrote:
> OK; supposing that input field is actually non-unicode (DT_STR could be the
> right data type), verify the transformation task to be sure about the output
> data type. SQL 2005 uses UNICODE by default, and you should explicitly
> declare non unicode data types.
> If you correct the input field definition specifying Unicode (DT_WSTR), you
> should avoid such error message.
> The fact that you are accessing input table via OLE DB does'nt change the
> fact that QSL 2005 uses Unicode by default.
> Gilberto Zampatti
>
> "SB" wrote:
>
>
> - Show quoted text -
Thanks.
For output I have created a excel table using the statement:
CREATE TABLE `Excel Destination` (
`NAME` VARCHAR(),
)
I have tried string, text etc but no success.
Excel Destination editor:
Data Access Mode:
Table or view
Name of the excel sheet:
Excel Destination

non unicode

Hi,
Is DT_STR Unicode? From SSIS I am using a data conversion component
and defining the data type as DT_STR and saving the output in excel
file. I am getting an error that 'can not convert from Unicode to non-
unicode'. I thought I have already taken care of that in data
conversion.Could you explain exactly which data conversion you prepared? The DT_STR was
specified for the input field or for the output? In this last case, which is
the input field data type?
Gilberto Zampatti
"SB" wrote:

> Hi,
> Is DT_STR Unicode? From SSIS I am using a data conversion component
> and defining the data type as DT_STR and saving the output in excel
> file. I am getting an error that 'can not convert from Unicode to non-
> unicode'. I thought I have already taken care of that in data
> conversion.
>|||On Apr 20, 2:10 pm, Gilberto Zampatti
<GilbertoZampa...@.discussions.microsoft.com> wrote:
> Could you explain exactly which data conversion you prepared? The DT_STR w
as
> specified for the input field or for the output? In this last case, which
is
> the input field data type?
> Gilberto Zampatti
>
> "SB" wrote:
> - Show quoted text -
Hi,
DT_STR should be an input field because I am getting data from a table
using ole db source.|||OK; supposing that input field is actually non-unicode (DT_STR could be the
right data type), verify the transformation task to be sure about the output
data type. SQL 2005 uses UNICODE by default, and you should explicitly
declare non unicode data types.
If you correct the input field definition specifying Unicode (DT_WSTR), you
should avoid such error message.
The fact that you are accessing input table via OLE DB does'nt change the
fact that QSL 2005 uses Unicode by default.
Gilberto Zampatti
"SB" wrote:

> On Apr 20, 2:10 pm, Gilberto Zampatti
> <GilbertoZampa...@.discussions.microsoft.com> wrote:
> Hi,
> DT_STR should be an input field because I am getting data from a table
> using ole db source.
>|||On Apr 20, 3:28 pm, Gilberto Zampatti
<GilbertoZampa...@.discussions.microsoft.com> wrote:
> OK; supposing that input field is actually non-unicode (DT_STR could be th
e
> right data type), verify the transformation task to be sure about the outp
ut
> data type. SQL 2005 uses UNICODE by default, and you should explicitly
> declare non unicode data types.
> If you correct the input field definition specifying Unicode (DT_WSTR), yo
u
> should avoid such error message.
> The fact that you are accessing input table via OLE DB does'nt change the
> fact that QSL 2005 uses Unicode by default.
> Gilberto Zampatti
>
> "SB" wrote:
>
>
>
> - Show quoted text -
Thanks.
For output I have created a excel table using the statement:
CREATE TABLE `Excel Destination` (
`NAME` VARCHAR(),
)
I have tried string, text etc but no success.
Excel Destination editor:
Data Access Mode:
Table or view
Name of the excel sheet:
Excel Destination|||On Apr 20, 3:28 pm, Gilberto Zampatti
<GilbertoZampa...@.discussions.microsoft.com> wrote:
> OK; supposing that input field is actually non-unicode(DT_STR could be the
> right data type), verify the transformation task to be sure about the outp
ut
> data type. SQL 2005 usesUNICODEby default, and you should explicitly
> declare nonunicodedata types.
> If you correct the input field definition specifyingUnicode(DT_WSTR), you
> should avoid such error message.
> The fact that you are accessing input table via OLE DB does'nt change the
> fact that QSL 2005 usesUnicodeby default.
> Gilberto Zampatti
>
> "SB" wrote:
>
>
>
> - Show quoted text -
Hi,
Is it alright if I declare a datatype as nvarchar 'create table (name
nvarchar(20))' and then treat it as if varchar for example functions,
inserts and join with other varchar columns etc? Except giving a
larger string size I don't have much use for unicode. They might as
well keep this type internal so that we didn't have to worry about it.|||On Apr 23, 10:41 am, SB <othell...@.yahoo.com> wrote:
> On Apr 20, 3:28 pm, Gilberto Zampatti
>
>
> <GilbertoZampa...@.discussions.microsoft.com> wrote:
>
>
>
>
>
> Hi,
> Is it alright if I declare a datatype as nvarchar 'create table (name
> nvarchar(20))' and then treat it as if varchar for example functions,
> inserts and join with other varchar columns etc? Except giving a
> larger string size I don't have much use for unicode. They might as
> well keep this type internal so that we didn't have to worry about it.- Hi
de quoted text -
> - Show quoted text -
That question was not related to SSIS package (as you have told ssis
is unicode). FWIW I was able to get the data (ssis) imported when I
used unicode for input and output data using a data conversion in
between from varchar to unicode string.

non unicode

Hi,
Is DT_STR Unicode? From SSIS I am using a data conversion component
and defining the data type as DT_STR and saving the output in excel
file. I am getting an error that 'can not convert from Unicode to non-
unicode'. I thought I have already taken care of that in data
conversion.Could you explain exactly which data conversion you prepared? The DT_STR was
specified for the input field or for the output? In this last case, which is
the input field data type?
Gilberto Zampatti
"SB" wrote:
> Hi,
> Is DT_STR Unicode? From SSIS I am using a data conversion component
> and defining the data type as DT_STR and saving the output in excel
> file. I am getting an error that 'can not convert from Unicode to non-
> unicode'. I thought I have already taken care of that in data
> conversion.
>|||On Apr 20, 2:10 pm, Gilberto Zampatti
<GilbertoZampa...@.discussions.microsoft.com> wrote:
> Could you explain exactly which data conversion you prepared? The DT_STR was
> specified for the input field or for the output? In this last case, which is
> the input field data type?
> Gilberto Zampatti
>
> "SB" wrote:
> > Hi,
> > Is DT_STR Unicode? From SSIS I am using a data conversion component
> > and defining the data type as DT_STR and saving the output in excel
> > file. I am getting an error that 'can not convert from Unicode to non-
> > unicode'. I thought I have already taken care of that in data
> > conversion.- Hide quoted text -
> - Show quoted text -
Hi,
DT_STR should be an input field because I am getting data from a table
using ole db source.|||OK; supposing that input field is actually non-unicode (DT_STR could be the
right data type), verify the transformation task to be sure about the output
data type. SQL 2005 uses UNICODE by default, and you should explicitly
declare non unicode data types.
If you correct the input field definition specifying Unicode (DT_WSTR), you
should avoid such error message.
The fact that you are accessing input table via OLE DB does'nt change the
fact that QSL 2005 uses Unicode by default.
Gilberto Zampatti
"SB" wrote:
> On Apr 20, 2:10 pm, Gilberto Zampatti
> <GilbertoZampa...@.discussions.microsoft.com> wrote:
> > Could you explain exactly which data conversion you prepared? The DT_STR was
> > specified for the input field or for the output? In this last case, which is
> > the input field data type?
> > Gilberto Zampatti
> >
> >
> >
> > "SB" wrote:
> > > Hi,
> > > Is DT_STR Unicode? From SSIS I am using a data conversion component
> > > and defining the data type as DT_STR and saving the output in excel
> > > file. I am getting an error that 'can not convert from Unicode to non-
> > > unicode'. I thought I have already taken care of that in data
> > > conversion.- Hide quoted text -
> >
> > - Show quoted text -
> Hi,
> DT_STR should be an input field because I am getting data from a table
> using ole db source.
>|||On Apr 20, 3:28 pm, Gilberto Zampatti
<GilbertoZampa...@.discussions.microsoft.com> wrote:
> OK; supposing that input field is actually non-unicode (DT_STR could be the
> right data type), verify the transformation task to be sure about the output
> data type. SQL 2005 uses UNICODE by default, and you should explicitly
> declare non unicode data types.
> If you correct the input field definition specifying Unicode (DT_WSTR), you
> should avoid such error message.
> The fact that you are accessing input table via OLE DB does'nt change the
> fact that QSL 2005 uses Unicode by default.
> Gilberto Zampatti
>
> "SB" wrote:
> > On Apr 20, 2:10 pm, Gilberto Zampatti
> > <GilbertoZampa...@.discussions.microsoft.com> wrote:
> > > Could you explain exactly which data conversion you prepared? The DT_STR was
> > > specified for the input field or for the output? In this last case, which is
> > > the input field data type?
> > > Gilberto Zampatti
> > > "SB" wrote:
> > > > Hi,
> > > > Is DT_STR Unicode? From SSIS I am using a data conversion component
> > > > and defining the data type as DT_STR and saving the output in excel
> > > > file. I am getting an error that 'can not convert from Unicode to non-
> > > > unicode'. I thought I have already taken care of that in data
> > > > conversion.- Hide quoted text -
> > > - Show quoted text -
> > Hi,
> > DT_STR should be an input field because I am getting data from a table
> > using ole db source.- Hide quoted text -
> - Show quoted text -
Thanks.
For output I have created a excel table using the statement:
CREATE TABLE `Excel Destination` (
`NAME` VARCHAR(),
)
I have tried string, text etc but no success.
Excel Destination editor:
Data Access Mode:
Table or view
Name of the excel sheet:
Excel Destination|||On Apr 20, 3:28 pm, Gilberto Zampatti
<GilbertoZampa...@.discussions.microsoft.com> wrote:
> OK; supposing that input field is actually non-unicode(DT_STR could be the
> right data type), verify the transformation task to be sure about the output
> data type. SQL 2005 usesUNICODEby default, and you should explicitly
> declare nonunicodedata types.
> If you correct the input field definition specifyingUnicode(DT_WSTR), you
> should avoid such error message.
> The fact that you are accessing input table via OLE DB does'nt change the
> fact that QSL 2005 usesUnicodeby default.
> Gilberto Zampatti
>
> "SB" wrote:
> > On Apr 20, 2:10 pm, Gilberto Zampatti
> > <GilbertoZampa...@.discussions.microsoft.com> wrote:
> > > Could you explain exactly which data conversion you prepared? The DT_STR was
> > > specified for the input field or for the output? In this last case, which is
> > > the input field data type?
> > > Gilberto Zampatti
> > > "SB" wrote:
> > > > Hi,
> > > > Is DT_STRUnicode? From SSIS I am using a data conversion component
> > > > and defining the data type as DT_STR and saving the output in excel
> > > > file. I am getting an error that 'can not convert fromUnicodeto non-
> > > >unicode'. I thought I have already taken care of that in data
> > > > conversion.- Hide quoted text -
> > > - Show quoted text -
> > Hi,
> > DT_STR should be an input field because I am getting data from a table
> > using ole db source.- Hide quoted text -
> - Show quoted text -
Hi,
Is it alright if I declare a datatype as nvarchar 'create table (name
nvarchar(20))' and then treat it as if varchar for example functions,
inserts and join with other varchar columns etc? Except giving a
larger string size I don't have much use for unicode. They might as
well keep this type internal so that we didn't have to worry about it.|||On Apr 23, 10:41 am, SB <othell...@.yahoo.com> wrote:
> On Apr 20, 3:28 pm, Gilberto Zampatti
>
>
> <GilbertoZampa...@.discussions.microsoft.com> wrote:
> > OK; supposing that input field is actually non-unicode(DT_STR could be the
> > right data type), verify the transformation task to be sure about the output
> > data type. SQL 2005 usesUNICODEby default, and you should explicitly
> > declare nonunicodedata types.
> > If you correct the input field definition specifyingUnicode(DT_WSTR), you
> > should avoid such error message.
> > The fact that you are accessing input table via OLE DB does'nt change the
> > fact that QSL 2005 usesUnicodeby default.
> > Gilberto Zampatti
> > "SB" wrote:
> > > On Apr 20, 2:10 pm, Gilberto Zampatti
> > > <GilbertoZampa...@.discussions.microsoft.com> wrote:
> > > > Could you explain exactly which data conversion you prepared? The DT_STR was
> > > > specified for the input field or for the output? In this last case, which is
> > > > the input field data type?
> > > > Gilberto Zampatti
> > > > "SB" wrote:
> > > > > Hi,
> > > > > Is DT_STRUnicode? From SSIS I am using a data conversion component
> > > > > and defining the data type as DT_STR and saving the output in excel
> > > > > file. I am getting an error that 'can not convert fromUnicodeto non-
> > > > >unicode'. I thought I have already taken care of that in data
> > > > > conversion.- Hide quoted text -
> > > > - Show quoted text -
> > > Hi,
> > > DT_STR should be an input field because I am getting data from a table
> > > using ole db source.- Hide quoted text -
> > - Show quoted text -
> Hi,
> Is it alright if I declare a datatype as nvarchar 'create table (name
> nvarchar(20))' and then treat it as if varchar for example functions,
> inserts and join with other varchar columns etc? Except giving a
> larger string size I don't have much use for unicode. They might as
> well keep this type internal so that we didn't have to worry about it.- Hide quoted text -
> - Show quoted text -
That question was not related to SSIS package (as you have told ssis
is unicode). FWIW I was able to get the data (ssis) imported when I
used unicode for input and output data using a data conversion in
between from varchar to unicode string.