September 5, 2012 at 10:34 am
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,
September 5, 2012 at 1:29 pm
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.
September 5, 2012 at 2:18 pm
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