Post reply

Problem In chageing collation

  • July 14, 2009 at 11:56 pm

    #132963

    I want to change collation. But it gives a messase as below

    The object 'SYS_C001619' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.

    I check and found that this is a check constraints Not NULL in a column.

    So How i go ahead to change collation

    Thanks

    KD

  • July 15, 2009 at 4:43 am

    #1024918

    Dear As I know Collation can only change on re installation.

    Thanks

  • July 15, 2009 at 6:26 am

    #1024959

    Hi KD,

    Does any object is dependent on this db? If so the collation settings can't be changed.

    Disable that dependency and then try to change the collation. Hope it will work.

  • July 15, 2009 at 6:34 am

    #1024966

    Hi Ajay,

    AFAIK, if we need to change the collation settings at SQL server level, we have to reinstall it, but change in database collation settings doesn't require reinstallation. It can be done using the Alter Database command.

  • July 15, 2009 at 7:17 am

    #1025010

    note that there are THREE places you can change collation at the server level, for each database, and then again for each column that is of type varchar/nvarchar/char/nchar/text.changing the collation at the database level only affects columns created in the future; it does not "fix" columns that already exist. you have to chang ethem seperately with an ALTER TABLE ALTER COLUMN command.

    if you had a check constraint on a varchar column for example, you'll want to do the following:

    1.script out the constriant,

    2.drop the constraint

    3.change the collation for the column in question

    4.add the constraint back

    proof of concept:

    CREATE TABLE [dbo].[MYEXAMPLE] (

    [EXAMPLEID] INT IDENTITY(1,1) NOT NULL,

    [EXAMPLEFLAG] CHAR(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    CONSTRAINT [PK__MYEXAMPLE__EXAMPLEID] PRIMARY KEY CLUSTERED (EXAMPLEID),

    CONSTRAINT [CK__MYEXAMPLE__EXAMPLEFLAG] CHECK ([EXAMPLEFLAG]='N' OR [EXAMPLEFLAG]='Y') )

    ALTER TABLE [MYEXAMPLE] ADD CONSTRAINT [DF__MYEXAMPLE__EXAMPLEFLAG] DEFAULT ('N') FOR EXAMPLEFLAG WITH VALUES

    GO

    /*error:

    Msg 5074, Level 16, State 1, Line 1

    The object 'CK__MYEXAMPLE__EXAMPLEFLAG' is dependent on column 'EXAMPLEFLAG'.

    Msg 4922, Level 16, State 9, Line 1

    ALTER TABLE ALTER COLUMN EXAMPLEFLAG failed because one or more objects access this column.

    */

    ALTER TABLE [MYEXAMPLE] DROP CONSTRAINT [CK__MYEXAMPLE__EXAMPLEFLAG]

    ALTER TABLE [MYEXAMPLE] ALTER COLUMN [EXAMPLEFLAG] CHAR(1) COLLATE SQL_Latin1_General_Cp1_CS_AS NULL

    ALTER TABLE [MYEXAMPLE] ADD CONSTRAINT [CK__MYEXAMPLE__EXAMPLEFLAG] CHECK ([EXAMPLEFLAG]='N' OR [EXAMPLEFLAG]='Y')

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 17, 2009 at 1:53 am

    #1026044

    Tnanks to all

    What if first we disable all check constraints with sp_MSforeachtable Procedure then chage collation and

    then again enable check constraints.

    Is there any risk to use sp_MSforeachtable procedure

  • July 18, 2009 at 1:29 pm

    #1026601

    I've been thru this procedure once due to the production and UAT servers having different collations and have worked out that you will need a set of processes defined to perform the following:

    script out all exisiting constraints

    script out all exisiting indexes

    drop all constraints

    drop all indexes

    Change Collation on Server

    Change Collation on Database

    Change Collation on all char/varchar/nchar etc colums to match datadase

    Reload Indexes

    Relead Constraints

  • July 21, 2009 at 12:34 am

    #1027425

    Thnaks a lot

  • July 28, 2009 at 5:38 am

    #1030962

    Cant we not change collation just disable that perticular constraints.

    I disable constraints SYS_C001619, but still cant alter table with new collation.

    If I drop this constraints and then try to change collation then it return that some otehr Primary_Key is dependent on it.

    Does it mean that I have to drop all constraint.

    Is there any other way

    Thanks

    KD

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

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