| Author |
Topic |
|
gooberpeas
Starting Member
5 Posts |
 Posted - 2007-12-06 : 22:29:21
|
| 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_ASfailed 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 1CREATE 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 1ALTER 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 - 2007-12-06 : 22:32:43
|
| p.s.I forgot to mention that I executed DBCC CHECKDB - no errors reported. |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-06 : 22:44:16
|
| 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. |
|
|
|
gooberpeas
Starting Member
5 Posts |
Posted - 2007-12-06 : 23:00:18
|
| 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 |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-06 : 23:25:18
|
| Didn't find it under system tables nor system views. |
|
|
|
gooberpeas
Starting Member
5 Posts |
Posted - 2007-12-07 : 10:26:27
|
| 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 |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-07 : 23:54:20
|
| You can query that table with back door way. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-12-12 : 23:15:41
|
| 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 oneHTH--------------------keeping it simple... |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-12 : 23:21:37
|
| It's system table. |
|
|
|
gooberpeas
Starting Member
5 Posts |
Posted - 2007-12-13 : 18:04:01
|
| 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 |
|
|
|
|
alter database/collate error