如何在SQL Server 2005上更改AdventureWorks数据库排序规则？

Question

所面临的挑战是要改变排序规则AdventureWorks数据库从Latin1_General_CS_AS到SQL_Latin1_General_CP1_CI_AS。有谁知道该怎么做？

我尝试这样做：

alter database AdventureWorks set single_user 
alter database AdventureWorks collate SQL_Latin1_General_CP1_CI_AS 
alter database AdventureWorks set multi_user 

但我得到以下错误：

Msg 5075, Level 16, State 1, Line 3
The object 'ufnLeadingZeros' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.

Msg 5075, Level 16, State 1, Line 3
The object 'CK_ProductReview_Rating' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.

Msg 5075, Level 16, State 1, Line 3
The object 'CK_TransactionHistory_TransactionType' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.

Msg 5075, Level 16, State 1, Line 3
The object 'CK_ProductVendor_AverageLeadTime' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.

然后我试图这样的事情：

DECLARE @NewCollation VARCHAR(255), @DBName sysname 

SELECT @NewCollation = 'SQL_Latin1_General_CP1_CI_AS', -- change this to the collation that you need 
     @DBName = DB_NAME() 

DECLARE @CName varchar(255), 
     @TbleName sysname, 
     @objOwner sysname, 
     @Sql varchar(8000), 
     @Size int, 
     @Status tinyint, 
     @Colorder int 

Declare CurWhileLoop cursor read_only forward_only local 
for Select 
     QUOTENAME(C.Name) 
     ,T.Name 
     ,QUOTENAME(U.Name) + '.' +QUOTENAME(O.Name) 
     ,C.Prec 
     ,C.isnullable 
     ,C.colorder 
    From syscolumns C 
     inner join systypes T on C.xtype=T.xtype 
     inner join sysobjects O on C.ID=O.ID 
     inner join sysusers u on O.uid = u.uid 
    where T.Name in ('varchar', 'char', 'text', 'nchar', 'nvarchar', 'ntext') 
     and O.xtype in ('U') 
     and C.collation != @NewCollation 
    and objectProperty(O.ID, 'ismsshipped')=0 
    order by 3, 1 

open CurWhileLoop 
SET XACT_ABORT ON 
begin tran 
fetch CurWhileLoop into @CName, @TbleName, @objOwner, @Size, @Status, @Colorder 
while @@FETCH_STATUS =0 
begin 
    set @Sql='ALTER TABLE '[email protected]+' ALTER COLUMN '[email protected]+' '[email protected]+ isnull ('(' 
+convert(varchar,@Size)+')', '') +' COLLATE '+ @NewCollation 
+' '+case when @Status=1 then 'NULL' else 'NOT NULL' end 
    exec(@Sql) -- change this to print if you need only the script, not the action 
    fetch CurWhileLoop into @CName, @TbleName, @objOwner, @Size, @Status, @Colorder 
end 
close CurWhileLoop 
deallocate CurWhileLoop 
commit tran 

而且我得到了以下错误：

Msg 4104, Level 16, State 1, Line 10
The multi-part identifier "U.Name" could not be bound.

我试图对AdventureWorks数据库运行这最后询问和我U.Name名称错误如上，当我试图运行对主数据库什么都不会发生此查询。

请帮忙！

Answer 1

建议从头开始。与上面的方法相比，您将花费更少的精力。

下载一个new copy/scripts for SQL Server 2005 AdventureWorks。

确保您的服务器默认具有您想要的排序规则。右键单击 - 属性 - >常规 - >服务器校对。

当您使用SQL Server Management Studio中的数据库，可以明确选择的归类。