Failure changing collation on restored database

Question

Failure changing collation on restored database

Kirk Yaros

SSC Enthusiast

Points: 185
More actions
July 3, 2007 at 1:36 pm

#117595

Hello everyone -
We are restoring an OLD (used to be a SQL 7) SQL 2000 database into SQL 2005 and attempting to change the collation from LATIN_GENERAL_BIN to SQL_Latin1_General_CP1_CI_AS. We are getting the following error on the ALTER DATABASE statement. We have done nothing other than backup / restore the database into the SQL 2005 instance.
"The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name "sys.syscolpars" and the index name 'nc'. The duplicate key is (ID, 1219639538,0). ALTER DATABASE failed"
Can anyone give any help / advice with any of the following questions:
1.) What is the "sys.syscolpars" object and where is it? We can't find it or find any reference as to what it is responsible for
2.) Any ideas on how to troubleshoot this? We can't run profiler because the ALTER DATABASE statement requires exclusive lock access.
3.) Any ideas on what objects would be duplicating on the ALTER statement which would cause the conflict?
Any help is appreciate on this, I'm pretty much at a loss for how to troubleshoot this issue.
Kirk

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

I will throw this one thought out to you. You are trying to change from a Case Sensitive collation to a Case Insensitive collation. In this database, you have at least one index with case sensitive data. Using a simplistic example: a and A are unique in a Case Sensitive collation, but in a Case Insensitve collation they aren't. If these are in an unique index, it can't be converted as you now have a duplicate key.

I would stop looking for sys.syscolpars for right now (perhaps someone will still answer this question, as I can't find it either in my systems) and start looking at your data so you can make appropriate changes before converting the data to the Case Insensitive collation.

I'll keep looking to see if I can find the sys.syscolpars table.

Kirk Yaros SSC Enthusiast Points: 185 More actions · Answer 2

Lynn -

After reading your post, that makes total sense. Although we thought we were going to reduce complexity by going to case - insensitive, we'll have some (brutal) clean-up of some of the unique keys. I'm going to start going down that path of checking all my Unique indexes that are character based...I bet that ends up being the issue. I'll post back if this ends up being the case.

Thanks! (Although the information you give me is not what I wanted to hear! )

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 3

Learned one more info about the collation and its effects in sql server today. thanks guys.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Jeff Gray SSChampion Points: 10667 More actions · Answer 4

Hello,

Alter database shouldn't have any effect on current user data. It's not going to change the collation of existing columns.

It might be changing the collation of system metadata tables, however. The table referenced in the error is one that holds the column names for every table and stored procedure in the database.

My guess is that a pair of column names somewhere in your database is no longer unique when you try to change to insensitive.

Consider this:

create

database foo

use

foo

alter

database foo

COLLATE

LATIN1_GENERAL_BIN

create

table bar (

COL1

int,

col1

int)

alter

database foo

COLLATE

SQL_Latin1_General_CP1_CI_AS

RESULTS:

Msg 1505, Level 16, State 1, Line 1

CREATE UNIQUE INDEX terminated because a duplicate key was found for object name 'sys.syscolpars' and index name 'nc'. The duplicate key value is (col1, 2073058421, 0).

Msg 5072, Level 16, State 1, Line 1

ALTER DATABASE failed. The default collation of database 'foo' cannot be set to SQL_Latin1_General_CP1_CI_AS.

The statement has been terminated.

noeld SSC Guru Points: 96590 More actions · Answer 5

The cleanest method to change the collation is

1. create a *new* database from the scripted old one

2. Populate the data on the new from the old

3. Drop old database

4. Rename new db to old name

Jeff is absolutely right when you run the ALTER to change the collation you are only changing metadata but your *data* is not changed at all.

Cheers,

* Noel

Jeff Gray SSChampion Points: 10667 More actions · Answer 6

After looking at the error message that the OP got and the one that I got in my simple recreation exercise it seems to me that one of the database objects probably has two columns named "ID", but with different cases. Perhaps "ID" and "id".

Anyway, it should be easy to find the offending object/name

select

so.name, UPPER(sc.name) from sys.syscolumns sc

inner

join sys.sysobjects so on so.id = sc.id

group

by so.name,UPPER(sc.name)

having

count(*) > 1

Kirk Yaros SSC Enthusiast Points: 185 More actions · Answer 7

Hello everyone -

Thanks for all the replies! They all pointed me in the right direction....in the end, I found out it was a View that had the same name as a table (after case-sensitivity was changed).

I found this through trial / error - eventually deleting all the data (still had the issue) then deleting all the views, and was able to change collation.

So - I'm good to go now, I have to fix a couple views but that is it.

Again- thanks for all the good info!

Kirk