Change Collation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Change Collation

Author

Topic

chriztoph
Posting Yak Master

184 Posts

Posted - 2014-06-17 : 05:10:30

Please help me solve this error.

I'm trying to change the default collation of the database from Latin1_General_BIN to SQL_Latin1_General_CP1_CI_AS.

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

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-06-17 : 06:24:32

You can add the conversion from the opposing db to the select/update:

SELECT

[Prefix] Collate SQL_Latin1_General_CP1_CI_AS as Prefix
, [FirstName] Collate SQL_Latin1_General_CP1_CI_AS as FirstName
,[LastName] Collate SQL_Latin1_General_CP1_CI_AS as LastName
From dbo.yourtable

We are the creators of our own reality!

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-06-17 : 06:34:06

Also, you can go to Database Properties - Options - Collation to change the collation type.

We are the creators of our own reality!

chriztoph
Posting Yak Master

184 Posts

Posted - 2014-06-17 : 21:19:14

quote:
Originally posted by sz1

Also, you can go to Database Properties - Options - Collation to change the collation type.

Got an error trying that.

TITLE: Microsoft SQL Server Management Studio
------------------------------

Alter failed for Database '<TESTDB>'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=12.0.2000.8+((SQL14_RTM).140220-1752)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The database could not be exclusively locked to perform the operation.
ALTER DATABASE failed. The default collation of database 'TESTDB' cannot be set to Latin1_General_CI_AS. (Microsoft SQL Server, Error: 5030)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=10.50.4000&EvtSrc=MSSQLServer&EvtID=5030&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

chriztoph
Posting Yak Master

184 Posts

Posted - 2014-06-17 : 23:09:46

Found the answer by finding the conflict index using

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME LIKE '%PK_TABLE1%'

and removed the key then change the collation of the database.

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-06-18 : 05:10:36

Nice to know :)

We are the creators of our own reality!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources