September 20, 2002 at 12:02 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
Gregory A. Larsen, MVP
September 21, 2002 at 10:18 am
Havent spent much time on collations. I use the default case insensitive, in some cases might make a column case sensitive. Not that this answers your question, but why do you care if they use the same collation or not?
Andy
September 22, 2002 at 4:36 pm
The reason I care is because we have cross database queries. When you have databases with a different character set, there are issues comparing data between database. To compare data you need to convert character data to a similar character set in order to do a compare. I'm at home now, but when I get back to work tommorrow, I will build and example for everyone.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 23, 2002 at 2:05 pm
Here is an example I promised related to more than one collating sequence per server.
Here is a query that shows the problem of having two database with different collating sequences does cause problems with join. Although if you use the COLLATE clause no problem is encountered. This will present a problem for our developers if we allow character data on our servers to have different collation sequences. I don't think our programmers would like having to code the COLLATE clause. Currently we have many application that cross database boundaries.
Example:
create database CHARSET1 collate SQL_Latin1_General_CP1_CI_AS
create database CHARSET2 collate SQL_Latin1_General_Pref_CP437_CI_AS
go
create table charset1.dbo.test (name char(10))
create table charset2.dbo.test (name char(10))
insert into charset1.dbo.test values('abcde')
insert into charset2.dbo.test values('abcde')
go
-- select with error
select * from charset1.dbo.test c1 join charset2.dbo.test c2
on c1.name = c2.name
-- select without error
select * from charset1.dbo.test c1 join charset2.dbo.test c2
on c1.name = c2.name collate SQL_Latin1_General_CP1_CI_AS
go
drop database charset1
drop database charset2
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply