Post reply

merge replication - "MSmerge_index_" The CREATE UNIQUE INDEX statement terminated

  • April 13, 2013 at 3:21 am

    #273590

    Hi,

    I am trying to create Merge replication with following steps and error below

    1. Create DB - Publication and Subscriber on same instance

    2. Create table with INT Identity primary key and few other nullable character columns - It has no FK.

    3. Added column ROWGUIDCOL manually in tables at both side

    4. updated ROWGUIDCOL with same value on both side

    5. Added publisher

    6. When i add subscriber, I am getting following error. I have tested this scenario with different table structure but of no use and even output file gives similar details

    Question: I just want to understand, on which column it is trying to create unique index?

    If ROWGUIDCOL has same value on both side and inface same table structure and data on both side and if merge replication checks for rowguid col for sync then what is the purpose of this error.

    Thanks for your help.

    Error messages:

    Message: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.myTable' and the index name 'MSmerge_index_856103282'. The duplicate key value is (<NULL>).

    The statement has been terminated.

    Command Text: sp_MSprepare_mergearticle

    Parameters: @source_owner = dbo

    @source_table = myTable

    @publication = Publication_Testing

    @qualified_tablename = [dbo].[myTable]

    Stack: at Microsoft.SqlServer.Replication.AgentCore.ReMapSqlException(SqlException e, SqlCommand command)

    at Microsoft.SqlServer.Replication.AgentCore.AgentExecuteNonQuery(SqlCommand command, Int32 queryTimeout)

    at Microsoft.SqlServer.Replication.AgentCore.ExecuteDiscardResults(CommandSetupDelegate commandSetupDelegate, Int32 queryTimeout)

    at Microsoft.SqlServer.Replication.Snapshot.ShilohPrepareMergeArticleWorkItem.DoVersionSpecificMergeArticlePreparation(SqlConnection connection)

    at Microsoft.SqlServer.Replication.RetryableSqlServerTransactionManager.ExecuteTransaction(Boolean bLeaveTransactionOpen)

    at Microsoft.SqlServer.Replication.Snapshot.PrepareMergeArticleWorkItem.PrepareMergeArticle(SqlConnection connection)

    at Microsoft.SqlServer.Replication.MainWorkerThreadWrapper.Run()

    at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.DoRegularMergeSnapshotPreparations()

    at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.DoPreArticleFilesGenerationProcessing()

    at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()

    at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()

    at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: MSSQLServer, Error number: 1505)

    Get help: http://help/1505

    Server Production, Level 0, State 0, Procedure , Line 1

    The statement has been terminated. (Source: MSSQLServer, Error number: 3621)

    Get help: http://help/3621

    Server Production, Level 0, State 0, Procedure , Line 1

    The statement has been terminated. (Source: MSSQLServer, Error number: 3621)

    Get help: http://help/3621%5B/quote%5D

  • April 13, 2013 at 9:55 am

    #1606253

    Ahh i got it .... there were few null rowguidcols which i have added after the update for testing

    select rowguidcol, count(1)

    from mytable

    group by rowguidcol

    having count > 1

    Thanks for viewing.

Viewing 2 posts - 1 through 1 (of 1 total)

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