An example of modifying database collate in SQL Server 2000

1. To confirm that no other person is connected to the current database, use sp_who to view the connection, and then use kill @ spid to forcibly close the connection.

2. Run the SQL statement to modify the database's collate attribute.

 

Use   [ Master ]
Go
Alter   Database   [ My_db ] Collate finnish_swedish_cs_as
Go

 

 

3. Obtain the original collate

 

Use   [ My_db ]
Select   Distinct Collationid From Syscolumns

 

4. Set to allow system table update. (Note that you cannot update the system table in SQL Server 2005 !)

 

Exec Sp_configure ' Allow updates ' , 1
Reconfigure With Override

 

5. Update the collationid obtained in step 3 to the new

 

Update Syscolumns Set Collationid =   49162   -- New
Where Collationid In ( 1359003656 ) -- Old

 

6. Disable system table update.

Exec Sp_configure ' Allow updates ' , 0
Reconfigure With Override

 

OK.