|
Home > Archive > microsoft.public.sqlserver.server > October 2002 > Changing Collation for a SQL 2000 DB.
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Changing Collation for a SQL 2000 DB.
|
|
| Greg Larsen 2002-10-05, 9:12 pm |
| I'm looking for different options to change the character
set and sort order
for SQL 2000 databases. Please offer your suggestions.
I''m migrating some databases to SQL 2000. The source of
these migrations
are from SQL 7.0 servers. The SQL 7.0 servers have a
different character
set/sort order, then the default character set/sort order
for the servers.
No problem migrating the data with a database backup,
since SQL 2000 allows
each database to have it''s own unique character set.
Although I would like
to eventually end up with all the databases on each SQL
2000 server to have
the same character set as the default server character
set.
I''m trying to compile all the possible ways to convert
from one character
set to another. I'm looking for your opinions on methods
of converting
these databases to the server character set/sort order.
Here are all the different method I have so far:
1) Export all the data from the SQL Server 7.0 database
and import into SQL
Server 2000 using the DTS Wizard.
2) Alter the collating sequence by doing the following:
- Issue "Alter Database collate
<collation_name>" to change
database collation.
- Drop all indexes, check constraint, foreign key
constraint, and
computed column
- Issue a "alter table alter column" of every column
of , varchar,
text, nchar, nvarchar, and ntext data type in database
- recreate indexes, recreate all indexes, check
constraints,
foreign_key contraints, and computed columns.
3) Use BCP and BULK INSERT to export and insert data from
a text file.
Are there others? Which method to you perfer? What are
the pros and cons
of each.
I'm leaning toward method 2. Only because I've
experienced trouble with DTS
Wizard failing, and I think BCP and BULK INSERT may take
more time.
I'm planning on building a utility to build the collation
conversion script,
with the appropriate drops, alters, and create
statements. Anyone already
written this, or have suggestions. How about has anyone
written an
automated BCP process to migrate data from on character
set to another? Any
know of any tools to help with this migration.
Your help and suggestions would be much appreciated.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my
website at
http://www.geocities.com/sqlserverexamples
| |
| Tibor Karaszi 2002-10-05, 9:12 pm |
| I'd go for Object Transfer in DTS. At least first shot, and watch out for errors and incorrect
character translations.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ublic.sqlserver
"Greg Larsen" <greg.larsen@doh.wa.gov> wrote in message
news:3f7c01c260e5$e78e86d0$2ae
2c90a@phx.gbl...
> I'm looking for different options to change the character
> set and sort order
> for SQL 2000 databases. Please offer your suggestions.
>
> I''m migrating some databases to SQL 2000. The source of
> these migrations
> are from SQL 7.0 servers. The SQL 7.0 servers have a
> different character
> set/sort order, then the default character set/sort order
> for the servers.
>
> No problem migrating the data with a database backup,
> since SQL 2000 allows
> each database to have it''s own unique character set.
> Although I would like
> to eventually end up with all the databases on each SQL
> 2000 server to have
> the same character set as the default server character
> set.
>
> I''m trying to compile all the possible ways to convert
> from one character
> set to another. I'm looking for your opinions on methods
> of converting
> these databases to the server character set/sort order.
>
> Here are all the different method I have so far:
>
> 1) Export all the data from the SQL Server 7.0 database
> and import into SQL
> Server 2000 using the DTS Wizard.
>
> 2) Alter the collating sequence by doing the following:
> - Issue "Alter Database collate
> <collation_name>" to change
> database collation.
> - Drop all indexes, check constraint, foreign key
> constraint, and
> computed column
> - Issue a "alter table alter column" of every column
> of , varchar,
> text, nchar, nvarchar, and ntext data type in database
> - recreate indexes, recreate all indexes, check
> constraints,
> foreign_key contraints, and computed columns.
>
> 3) Use BCP and BULK INSERT to export and insert data from
> a text file.
>
> Are there others? Which method to you perfer? What are
> the pros and cons
> of each.
>
> I'm leaning toward method 2. Only because I've
> experienced trouble with DTS
> Wizard failing, and I think BCP and BULK INSERT may take
> more time.
>
> I'm planning on building a utility to build the collation
> conversion script,
> with the appropriate drops, alters, and create
> statements. Anyone already
> written this, or have suggestions. How about has anyone
> written an
> automated BCP process to migrate data from on character
> set to another? Any
> know of any tools to help with this migration.
>
> Your help and suggestions would be much appreciated.
>
>
> Gregory Larsen, DBA
>
> If you looking for SQL Server Examples check out my
> website at
> http://www.geocities.com/sqlserverexamples
>
|
|
|
|
|