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-ANSI Outer Join in MSSQL2K5

Hi,
When we ran upgrade advisor for 2005 against our existing database, we
received the following error message.
The query uses non-ANSI outer join operators ("*="or "=*"). to run this
query without modification, please set the compatiblity level for current
database to 80 or lower, using stored procedure SP_dbcmptlevel. it is
strongly recommended to rewrite the query using ANSI outer join operators
(LEFT OUTER JOIN, RIGTH OUTER JOIN). in the future versions of SQL server,
non-ANSI join operators will not be supported even in backward compatibility
modes.
We have quite a few files/objects where we have the non-ANSI standard outer
join syntax that we would have to convert to use the ANSI outer join. Does
anyone know of any tool that will automatically convert out non-ANSI outer
join to the ANSI one? This would definitely save us a lot of time during our
migration to SQL Server 2005. Any help is appreciated.
Thanks,
Dee
I dont know of such a tool. One of the reasons why the old syntax is
deprecated is because of its inherent ambiguity. There has never been a
formal, standard definition of how to interpret queries that contain both
inner and outer style predicates in the WHERE clause. For that reason any
automated method of conversion to the new syntax probably couldn't be 100%
reliable.
David Portas
SQL Server MVP

Non-ANSI Outer Join in MSSQL2K5

Hi,
When we ran upgrade advisor for 2005 against our existing database, we
received the following error message.
The query uses non-ANSI outer join operators ("*="or "=*"). to run this
query without modification, please set the compatiblity level for current
database to 80 or lower, using stored procedure SP_dbcmptlevel. it is
strongly recommended to rewrite the query using ANSI outer join operators
(LEFT OUTER JOIN, RIGTH OUTER JOIN). in the future versions of SQL server,
non-ANSI join operators will not be supported even in backward compatibility
modes.
We have quite a few files/objects where we have the non-ANSI standard outer
join syntax that we would have to convert to use the ANSI outer join. Does
anyone know of any tool that will automatically convert out non-ANSI outer
join to the ANSI one? This would definitely save us a lot of time during ou
r
migration to SQL Server 2005. Any help is appreciated.
Thanks,
DeeI dont know of such a tool. One of the reasons why the old syntax is
deprecated is because of its inherent ambiguity. There has never been a
formal, standard definition of how to interpret queries that contain both
inner and outer style predicates in the WHERE clause. For that reason any
automated method of conversion to the new syntax probably couldn't be 100%
reliable.
David Portas
SQL Server MVP
--

Non-ANSI Outer Join in MSSQL2K5

Hi,
When we ran upgrade advisor for 2005 against our existing database, we
received the following error message.
The query uses non-ANSI outer join operators ("*="or "=*"). to run this
query without modification, please set the compatiblity level for current
database to 80 or lower, using stored procedure SP_dbcmptlevel. it is
strongly recommended to rewrite the query using ANSI outer join operators
(LEFT OUTER JOIN, RIGTH OUTER JOIN). in the future versions of SQL server,
non-ANSI join operators will not be supported even in backward compatibility
modes.
We have quite a few files/objects where we have the non-ANSI standard outer
join syntax that we would have to convert to use the ANSI outer join. Does
anyone know of any tool that will automatically convert out non-ANSI outer
join to the ANSI one? This would definitely save us a lot of time during our
migration to SQL Server 2005. Any help is appreciated.
Thanks,
DeeI dont know of such a tool. One of the reasons why the old syntax is
deprecated is because of its inherent ambiguity. There has never been a
formal, standard definition of how to interpret queries that contain both
inner and outer style predicates in the WHERE clause. For that reason any
automated method of conversion to the new syntax probably couldn't be 100%
reliable.
--
David Portas
SQL Server MVP
--

No tool to upgrade Access databases

sql server 2k has a tool to upgrade Access databases. I can't find any similar tool in sql server 2005. Am I missing something?

dennist

Do you mean the Import/Export Wizard? This will copy some of the schema and the data from an Access Database. Right click on a Database in SSMS and select import.

If you mean the Access Upsizing wizard this is part of Access and we are still closing those plans.

-Euan

Please reply only to the newsgroup/forum so that others can benefit. When posting,please state the version of SQL Server being used and the error number/exact error message text received, if any.

Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

This posting is provided "AS IS" with no warranties, and confers no rights.