Collation Settings

[Al]

New SQL2000 installation into which I've copied data from multiple SQL7 installations. Some of the databases I've copied over from a SQL7 server with code page 850, sort order/collation 42, using the detach-copy mdf/ldf-attach procedure outlined elsewhere on this site (thank you). Other databases have been DTS'd from another SQL7 server with code page 850, sort order/collation 44.

In the end I want all databases to be in the default Latin1_General_CI_AI collation I've chosen for my installation.

I'm off to immerse myself in collation papers and alter database documentation from BOL, but was hoping someone could enlighten me in a less painful way.

Thanks,

Al

[Gary Andrews]

I have no experience in this area, but have spent an ample amount of time reading in this area.

I believe the following to be true.

When SQL 2000 is installed you pick the collation for the installation which is known as the default collation. This is the collation used to create the Master, Model, and Msdb (tempdb ?).

SQL 2000 allows you to specify the collation for databases and columns.

If you don't specify otherwise when creating a database, then you get the default collation. Same for defining columns in tables. If you don't specify otherwise, you get the default.

It seems to me, that if you create a database specifying the collation you desire, then copy your input into that database, that the data should then end up in the collation you want. You could then delete the original database from the instance. Maybe even rename the created database to the name of the deleted database.

There may be a couple more details to be worked out, but it seems that this should work in general.

A possible alternative you might try, after backing up the database, is to use the alter database statement to modify the table columns collations. I don't favor this approach because it seems to me that when a new table is created, the database is still going to create it with its default collation, which is not changed by changing the table column collations of the original data.

Please keep me informed of how you solve this problem, if you would be so kind.

Gary Andrews
andrews_gary_w@solarturbines.com
------------
Al at 7/16/2002 1:57:56 PM

New SQL2000 installation into which I've copied data from multiple SQL7 installations. Some of the databases I've copied over from a SQL7 server with code page 850, sort order/collation 42, using the detach-copy mdf/ldf-attach procedure outlined elsewhere on this site (thank you). Other databases have been DTS'd from another SQL7 server with code page 850, sort order/collation 44.

In the end I want all databases to be in the default Latin1_General_CI_AI collation I've chosen for my installation.

I'm off to immerse myself in collation papers and alter database documentation from BOL, but was hoping someone could enlighten me in a less painful way.

Thanks,

Al

[olutimi]

Check this article it was helpful.

http://www.databasejournal.com/featu...0894_2013741_2

Regards