Replication errors after changing PK on table

  • Ok,

    So I had to make some schema changes on a table. So I dropped the article, re-add, re-sync, etc, but now I am getting this error:

    cannot update identity column

    Here's the T-SQL script I used:

    --Turning off sync

    EXEC sp_changepublication

    @publication = 'My_Publication',

    @property = N'allow_anonymous',

    @value = 'false'

    GO

    EXEC sp_changepublication

    @publication = 'My_Publication',

    @property = N'immediate_sync',

    @value = 'false'

    GO

    --Drop the article from publication

    USE [Database]

    EXEC sp_dropsubscription

    @publication = 'My_Publication',

    @article = N'MyTable',

    @subscriber = server1;

    GO

    --Make article invalid

    USE [Database]

    EXEC sp_droparticle

    @publication = 'My_Publication',

    @article = 'MyTable',

    @force_invalidate_snapshot = 1;

    GO

    /*

    Here, we change or drop the table. Or whatever schema change is needed.

    */

    --Now, we add the article back.

    EXEC sp_addarticle

    @publication = 'My_Publication',

    @article = 'MyTable',

    @source_object = 'MyTable',

    @force_invalidate_snapshot = 1

    --Refresh the subscription

    EXEC sp_refreshsubscriptions @publication = 'My_Publication'

    --Enable sync

    EXEC sp_changepublication

    @publication = 'My_Publication',

    @property = N'immediate_sync',

    @value = 'true'

    GO

    EXEC sp_changepublication

    @publication = 'My_Publication',

    @property = N'allow_anonymous',

    @value = 'true'

    GO

    In terms of table schema changes, what I basically did, was dropping the PK and recreate it again, on source database, of course.

    What I'm missing here, why the replication broke ...

    Thanks in advance,

  • You need to modify the system created Stored Proc sp_MSupd_MyTable in the subscriber database by remove the key columns from updating.

    Hope this will help.

  • yu.he (9/5/2012)


    You need to modify the system created Stored Proc sp_MSupd_MyTable in the subscriber database by remove the key columns from updating.

    Hope this will help.

    Thanks,

    Can you elaborate more, please ... both (subscriber and publication) are on same server ...

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

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