New Merge setup with Error QUestions

Discussion in 'microsoft.public.sqlserver.replication' started by Darin, Jan 2, 2004.

  1. Darin

    Darin Guest

    I have an existing database that I am setting up for Merge replication.
    I have modified all IDENTITY columns to be NFR, and added a rowguid
    default(newid()) to EVERY table. I setup the publisher, all is fine. I
    then setup the publication and I get an error:

    CREATE UNIQUE INDEX terminated because a buplicate key was found for
    index ID 3. Most significant primary key is '<NULL>'.

    I setup the agent output and using that found the file it is trying to
    setup (this is the first file).

    I then through Enterprise manager scripted that table with all indexs,
    defaults, etc for you to see:

    CREATE TABLE [dbo].[UTUser] (
    [usr_id] [int] IDENTITY (1, 2) NOT FOR REPLICATION NOT NULL ,
    [usr_login] [dt10char] NOT NULL ,
    [usr_favdesc] [dt50char] NULL ,
    [usr_favshortcut] [int] NULL ,
    [usr_favtag] [dt30char] NOT NULL ,
    [usr_rowguid] uniqueidentifier ROWGUIDCOL NULL
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[UTUser] WITH NOCHECK ADD
    CONSTRAINT [PK_UTUser] PRIMARY KEY CLUSTERED
    (
    [usr_id]
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[UTUser] ADD
    CONSTRAINT [DF__UTUser__usr_rowg__67267583] DEFAULT (newid()) FOR
    [usr_rowguid]
    GO

    As you can see, there is only one key and it is the primary key.

    The data table has 211 rows in it, and the only column that has NULL is
    the rowguid, and that is because the column was just added.

    What do I do to correct/get around this error?

    TIA

    Darin
     
    Darin, Jan 2, 2004
    #1
    1. Advertisements

  2. How did you add the uniqueidentifier column? Did you do it through the
    'Design table' wizard in the SQL enterprise manager or through alter table
    statement in query analyzer. If you did it through SQL enterprise manager
    you should uncheck the box 'Allow Nulls' so that the existing rows do not
    get null values for the new column. Two NULLS in the same column wold cause
    the unique index creation to fail. If you are doing it through query
    analyzer make sure you specify NOT NULL when you add the new column.

    HTH.
    Ramu.
    This posting is provided "AS IS" with no warranties, and confers no rights.
     
    Ramakrishna Konidena [MS], Jan 2, 2004
    #2
    1. Advertisements

  3. Darin

    Darin Guest

    I created all of the rowguid's columns via QA and said NULL - what is
    the easiest (quickest) way to just change those columns to NOT NULL?
    Just going into EM and changing it there generates a cannot insert null
    value error.

    Darin
     
    Darin, Jan 3, 2004
    #3
  4. I guess you can delete the column and recreate it with NOT NULL option. You
    can delete the column in the SQL enterprise manager using the Design table
    interface by higlighting the column and pressing the delete key or by using
    Alter table drop column in query analyzer.

    HTH.
    -Ramu.
    This posting is provided "AS IS" with no warranties, and confers no rights.
     
    Ramakrishna Konidena [MS], Jan 5, 2004
    #4
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.