Changing collation

Question

Post reply

Changing collation

raptorkiller47

SSChasing Mays

Points: 627
More actions
June 2, 2004 at 9:00 pm

#84592

Hi everyone,
I just installed SQL Server 2000 Standard version and during the installation, there was no option for me to pick the type of collation that I'd like to use. Does anyone know how to change the collation after the installation? I guess one should be able to change collation in Analyser Query but I dont know how to do this. Please help anyone.
Thank you

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 1

Hi,

This is the information provided from BOL:

Changing Collation Settings After Installing

Collation settings, which include character set, sort order, and other locale-specific settings, are fundamental to the structure of all Microsoft® SQL Server™ 2000 databases. To change one or more of these settings, you must rebuild the master and user databases.

Shutdown Microsoft® SQL Server™ 2000, and then run Rebuildm.exe. This is located in the Program Files\Microsoft SQL Server\80\Tools\Binn directory.
In the Rebuild Master dialog box, click Browse.
In the Browse for Folder dialog box, select the \Data folder on the SQL Server 2000 compact disc or in the shared network directory from which SQL Server 2000 was installed, and then click OK.
Click Settings. In the Collation Settings dialog box, verify or change settings used for the master database and all other databases.
Initially, the default collation settings are shown, but these may not match the collation selected during setup. You can select the same settings used during setup or select new collation settings. When done, click OK.
In the Rebuild Master dialog box, click Rebuild to start the process.
The Rebuild Master utility reinstalls the master database.

Note To continue, you may need to stop a server that is running.

When you rebuild the master database, only the collation of the master database, the model database, and the msdb database is changed. The collation of the user databases is not changed. To change the collation of an existing user database or to create a new database with the appropriate collation, use the ALTER DATABASE command, and then use DTS or the bcp utility to transfer the data to the new database.

Note If you use the ALTER DATABASE command in SQL Server 2000 to change the collation of a database, the collation of the columns in the tables is not automatically changed. To change the collation of the columns, use the ALTER TABLE command and the ALTER COLUMN command. If you are using DTS, you can create the table and the columns with the appropriate collation before you transfer the data or you can use the Use Collation option. If you are using DTS and the table with the appropriate collation already exists, make sure to disable the Drop Existing Objects First option before you run the package.

Hope this suffices your requirement to change the collation.

Lucky

Angela But Hall of Fame Points: 3848 More actions · Answer 2

Just for future reference, when you install SQL Server 2000 you must choose "custom installation" to be able to choose a collation.

As Lucky has posted, you can either rebuild master, specify the collation when you create database/tables or completely reinstall. My preference is to reinstall as I've had problems trying to rebuild master in the past (and it's more fiddling around than a reinstall if you have nothing on your server yet).

If you specify the collation when you create a database or a table, you must be careful when using tempdb (such as temp tables) as tempdb will use the default server collation and you may recieve an error.

Cheers,

Angela

Changing collation

Changing Collation Settings After Installing

See Also