SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Other SQL Server Topics (2005)
 alter database/collate error
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gooberpeas
Starting Member

5 Posts

Posted - 12/06/2007 :  22:29:21  Show Profile  Reply with Quote
We're upgrading to SQL Server 2005, and we've restored databases from SQL Server 2000 to the development server (SQL Server 2005). Part of what we need to do is alter the database collation to SQL_Latin1_General_CP1_CI_AS; our SQL Server 2000 server is case-sensitive.

My initial attempts to alter the database;

alter database RGDWDEV collate SQL_Latin1_General_CP1_CI_AS

failed because of the presence of computed columns and user functions. So, I wrote scripts to save the definitions of the computed columns and functions, and wrote scripts to drop them. Now that they are gone, I expected to be able to successfully execute the above 'alter database' statement, but I am now getting this error;

Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for object name 'sys.sysschobjs' and index name 'nc1'. The duplicate key value is (0, 1, DISB_ADJ).
Msg 5072, Level 16, State 1, Line 1
ALTER DATABASE failed. The default collation of database 'RGDWDEV' cannot be set to SQL_Latin1_General_CP1_CI_AS.


I do not know how to fix this.

Can anyone help me?

thank you, beth

gooberpeas
Starting Member

5 Posts

Posted - 12/06/2007 :  22:32:43  Show Profile  Reply with Quote
p.s.

I forgot to mention that I executed DBCC CHECKDB - no errors reported.
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 12/06/2007 :  22:44:16  Show Profile  Reply with Quote
Ther are dup values in the column. For instance, Microsift is different from microsoft in case sensitive collation but are same in case insensitice collation.
Go to Top of Page

gooberpeas
Starting Member

5 Posts

Posted - 12/06/2007 :  23:00:18  Show Profile  Reply with Quote
I understand that, but how do I find out where the duplicate is? sys.sysschobjs is, I believe, a system table. What sort of data is stored in this table?

thank you, beth
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 12/06/2007 :  23:25:18  Show Profile  Reply with Quote
Didn't find it under system tables nor system views.
Go to Top of Page

gooberpeas
Starting Member

5 Posts

Posted - 12/07/2007 :  10:26:27  Show Profile  Reply with Quote
I've done a google search - sys.sysschobjs is a system table, new under SQL Server 2005, and hidden, which explains why you can't find it. There is apparently a back-door way to get at hidden system tables, but I do not want to do that.

I don't see how I'm going to figure out what sort of data the server is storing in this table, and then back-track to find the object that it thinks is a duplicate. If I could locate the problem object, I could delete or rename it.

Does anyone know how to solve this problem?

thanks, beth
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 12/07/2007 :  23:54:20  Show Profile  Reply with Quote
You can query that table with back door way.
Go to Top of Page

jen
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 12/12/2007 :  23:15:41  Show Profile  Send jen a Yahoo! Message  Reply with Quote
did you try to recreate the table (with a different name and right collation)?

if it was successful then just copy over the data from the old table to the new one

HTH

--------------------
keeping it simple...
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 12/12/2007 :  23:21:37  Show Profile  Reply with Quote
It's system table.
Go to Top of Page

gooberpeas
Starting Member

5 Posts

Posted - 12/13/2007 :  18:04:01  Show Profile  Reply with Quote
FYI - problem solved, sort of. Our DBA put in a problem report with Microsoft several days ago. He got a call back today - it's an SQL Server 2005 bug; no fix for it yet. We'll have to approach altering collation a different way.

thanks, beth
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000