convert tables in a database to use unicode

Hi!
I have to convert all nonUnicode-fields in a database to use unicode. I
tried to convert one column and realized that it will take very long time or
the memory usage will be too high.
If I create a database (a copy of the one that is used) and create tables
that use unicode in this one and then export the data from the old db to the
new db, is this a good idea? Are there any better ways ( there always are
:-), anyone who has more experience than me?)
Thanks for help!
//Malin
this is how I would do it.
script out all the objects into individual scripts.
create a vb script to search and replace varchar with nvarchar, char with
nchar and text with ntext.
build a database using the scripts.
run a comparison and synchronization on using the newly created database as
the source and the target which would be a copy of the entended database and
record the delta script.
Job done using DB Ghost.
Although the above is certainly possible it lacks any change management. If
you have all your source code in source control the changes could be
automatically made by checking out all the source and running the procedures
above. You'd then have a history of what is changing with your database code
via all the functions of your source control - such as who changed this? why
was it changed? when was it changed? how was it changed? where was it changed?
most people use source control for procedural code - why not database code?
DB Ghost gives you a fast, easy way to manage your database code using your
favorite source control.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Malin Davidsson" wrote:

> Hi!
> I have to convert all nonUnicode-fields in a database to use unicode. I
> tried to convert one column and realized that it will take very long time or
> the memory usage will be too high.
> If I create a database (a copy of the one that is used) and create tables
> that use unicode in this one and then export the data from the old db to the
> new db, is this a good idea? Are there any better ways ( there always are
> :-), anyone who has more experience than me?)
> Thanks for help!
> //Malin
>
>
|||Hmm... that's a very complex solution to a very simple problem.
Here's a much simpler way of achieving this goal:
1. Backup your DB if possible.
2. Use a simple T-SQL script that converts all VARCHAR fields to NVARCHAR.
Here, the script must take into account the size of the table row, to ensure
that you do not exceed 8060 bytes. This is quite simple to do though.
Run this script to loop over all DB tables.
Question: Do you also need to convert TEXT fields to NTEXT, or only from
VARCHAR to NVARCHAR?
I can help out with the script, if you need assistance.
Omri.
Omri Bahat
SQL Farms Solutions
www.sqlfarms.com
|||my main point in doing it this way is to have all changes under source
control...
"Omri Bahat" wrote:

> Hmm... that's a very complex solution to a very simple problem.
> Here's a much simpler way of achieving this goal:
> 1. Backup your DB if possible.
> 2. Use a simple T-SQL script that converts all VARCHAR fields to NVARCHAR.
> Here, the script must take into account the size of the table row, to ensure
> that you do not exceed 8060 bytes. This is quite simple to do though.
> Run this script to loop over all DB tables.
> Question: Do you also need to convert TEXT fields to NTEXT, or only from
> VARCHAR to NVARCHAR?
> I can help out with the script, if you need assistance.
> Omri.
> --
> Omri Bahat
> SQL Farms Solutions
> www.sqlfarms.com
>
|||Hi!
Yes I also want to change text to ntext, how do I do that? I tried alter
table but noticed tha I'm not allowed to change text-columns... :-/
thanks for all help!
//Malin
"Omri Bahat" <OmriBahat@.discussions.microsoft.com> wrote in message
news:06EAC641-ECFD-4E77-B8FE-01E5D8A83326@.microsoft.com...
> Hmm... that's a very complex solution to a very simple problem.
> Here's a much simpler way of achieving this goal:
> 1. Backup your DB if possible.
> 2. Use a simple T-SQL script that converts all VARCHAR fields to NVARCHAR.
> Here, the script must take into account the size of the table row, to
> ensure
> that you do not exceed 8060 bytes. This is quite simple to do though.
> Run this script to loop over all DB tables.
> Question: Do you also need to convert TEXT fields to NTEXT, or only from
> VARCHAR to NVARCHAR?
> I can help out with the script, if you need assistance.
> Omri.
> --
> Omri Bahat
> SQL Farms Solutions
> www.sqlfarms.com
>