Shulou(Shulou.com)06/01 Report--

How to change the collation of SQL SERVER 2000

Alter datebase Alter datebase database Chinese_PRC_BIN

ALTER TABLE tb

ALTER COLUMN colname nvarchar (100) COLLATE Chinese_PRC_CI_AS

-- case insensitive

ALTER TABLE tb

ALTER COLUMN colname nvarchar (100) COLLATE Chinese_PRC_CS_AS

-- case sensitive

You can get more rules by using the following command:

SELECT *

FROM:: fn_helpcollations ()

After changing the database collation, the collation of the fields in the table remains the same, so it is too tiring to change one field to another in the interface of the design table in Enterprise Manager.

EXEC sp_configure 'allow updates',1 RECONFIGURE WITH OVERRIDE

Update dbo.syscolumns set collationid=65572 where collationid=53284

EXEC sp_configure 'allow updates',0 RECONFIGURE WITH OVERRIDE

Go

When changing the collation of a database, make sure that your database does not have any connections.

It is best to use the following method in the query analyzer, paying attention to changing the database name:

/ *

Turn off process processing opened by the user

, /

Use master

Go

If exists (select * from dbo.sysobjects where id = object_id (N' [dbo]. [p_killspid]') and OBJECTPROPERTY (id, NumbIsProcedure') = 1)

Drop procedure [dbo]. [p_killspid]

GO

Create proc p_killspid

@ dbname varchar (200)-the database name of the process to shut down

As

Declare @ sql nvarchar

Declare @ spid nvarchar (20)

Declare # tb cursor for

Select spid=cast (spid as varchar 20) from master..sysprocesses where dbid=db_id (@ dbname)

Open # tb

Fetch next from # tb into @ spid

While @ @ fetch_status=0

Begin

Exec ('kill' + @ spid)

Fetch next from # tb into @ spid

End

Close # tb

Deallocate # tb

Go

-- close user connection

Exec p_killspid 'database name'

Go

-- modify collation

Alter datebase Alter datebase database name Chinese_PRC_BIN

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.