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 2008 Forums
 SQL Server Administration (2008)
 Change Collation
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

chriztoph
Posting Yak Master

Philippines
184 Posts

Posted - 06/17/2014 :  05:10:30  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

United Kingdom
392 Posts

Posted - 06/17/2014 :  06:24:32  Show Profile  Reply with Quote
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!
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
392 Posts

Posted - 06/17/2014 :  06:34:06  Show Profile  Reply with Quote
Also, you can go to Database Properties - Options - Collation to change the collation type.

We are the creators of our own reality!
Go to Top of Page

chriztoph
Posting Yak Master

Philippines
184 Posts

Posted - 06/17/2014 :  21:19:14  Show Profile  Reply with Quote
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
------------------------------
Go to Top of Page

chriztoph
Posting Yak Master

Philippines
184 Posts

Posted - 06/17/2014 :  23:09:46  Show Profile  Reply with Quote
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.
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
392 Posts

Posted - 06/18/2014 :  05:10:36  Show Profile  Reply with Quote
Nice to know :)

We are the creators of our own reality!
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.06 seconds. Powered By: Snitz Forums 2000