Resolve Collation Conflict problem in T-SQL (SHUBHAM SAXENA)

Question

Resolve Collation Conflict problem in T-SQL (SHUBHAM SAXENA)

shubham.saxena67

Default port

Points: 1490
More actions
May 2, 2012 at 1:33 am

#260238

Hello Friends,
I want to alter my table column collation to do such task when i run
ALTER TABLE City_master ALTER COLUMN city_id VARCHAR(20) COLLATE Latin1_General_CS_AS
i gets an error:
Msg 5074, Level 16, State 1, Line 1
The object 'PK_CITY_ID' is dependent on column 'city_id'.
Msg 5074, Level 16, State 1, Line 1
The index 'IND_HIM8' is dependent on column 'city_id'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN city_id failed because one or more objects access this column.
Please help i want to change collation on column city_id without delete my data or move my data to
other table.
Thanks!!

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

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

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

You don't need to delete the data. You have to drop the primary key and the index IND_HIM8 before modifying the collation. After you create it you can (and should) rebuild the primary key and the index.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

shubham.saxena67 Default port Points: 1490 More actions · Answer 2

Dear friend,

plz tell me how to drop primary key 'IND_HIM8' on table City_Master CITY_ID Column..

Plz send me Script to drop primary key..

Thanks

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

open management studio --> expand databases --> expand the database --> expand tables --> expand the table -->

expand keys --> right click the key --> script key as drop and create to --> new query window

open management studio --> expand databases --> expand the database --> expand tables --> expand the table --> expand indexes --> right click the index --> script index as drop and create to --> new query window

shubham.saxena67 Default port Points: 1490 More actions · Answer 4

shubham.saxena67

Default port

Points: 1490

May 2, 2012 at 4:17 am

#1482264

Hi Friend,

Thank You Sooooooooo Much...:-)

vinu512 SSCoach Points: 15729 More actions · Answer 5

shubham.saxena67 (5/2/2012)
Dear friend,
plz tell me how to drop primary key 'IND_HIM8' on table City_Master CITY_ID Column..
Plz send me Script to drop primary key..
Thanks

ALTER TABLE <table_name>

DROP CONSTRAINT <Primarykey_name>

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

shubham.saxena67 Default port Points: 1490 More actions · Answer 6

Hi Friend,

But I am working on SQL Azure and in that portal i am not able to generate script

it shows following error

TITLE: Microsoft SQL Server Management Studio

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

Script failed for Server 'cdt8gmxcg7.database.windows.net,1433'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1540+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Script+Server&LinkId=20476

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

ADDITIONAL INFORMATION:

Attempt to retrieve data for object failed for Server 'cdt8gmxcg7.database.windows.net,1433'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1540+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attempt+to+retrieve+data+for+object+Server&LinkId=20476

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

Index was outside the bounds of the array. (Microsoft.SqlServer.Smo)

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

BUTTONS:

OK

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

Plz provide me some solution why ssms throw this error in Azure Database..

Thanks a lot in advance..

Ant-Green SSC Guru Points: 113507 More actions · Answer 7

as vinu pointed out you will need to use alter table drop constraint and the drop index commands

please ensure that you know what the definition of these objects are before dropping them as you wont be able to recreate them once they are dropped if you dont know which columns to recreate them against

PK

ALTER TABLE tablename DROP CONSTRAINT constraintname

Index

DROP INDEX indexname ON tablename