Modify the database COLLATE instance in SQLServer2000

1. make sure that no other person is connected to the current database. you can 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 Collate attribute of the database, USE [master] GOALTERDATABASE [My_DB] COLLATEFinnish_Swedish_CS_ASGO3. obtain the original CollateUse [My_DB] selectdistinct

1. make sure that no other person is connected to the current database. you can 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 Collate attribute of the DATABASE. USE [master] GO ALTER DATABASE [My_DB] COLLATE Finnish_Swedish_CS_AS GO 3. obtain the original Collate Use [My_DB] select distinct

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.

,