October 3, 2014 at 5:40 am
Hello,
I have been attempting to add a column to a published database and then get the change to replicate to the subscriber.
I have read a few articles and used the process described here: http://msdn.microsoft.com/en-gb/library/ms147331(v=sql.105).aspx
My details are as follows:
- SQL 2008R2 SP1
- transactional replication
- subscriber created from backup
- replicate DDL enabled
- replicate schema changes enabled
I have run the following which all complete successfully, adding the new column to the table and then the article:
EXEC sp_changepublication
@publication = 'IM_Audit_LIVE',
@property = N'replicate_ddl',
@value = 0
GO
ALTER TABLE dbo.AuditSource ADD TEST VARCHAR(20) NULL ;
GO
EXEC sp_articlecolumn
@publication = 'IM_Audit_LIVE',
@article = 'AuditSource';
EXEC sp_changepublication
@publication = 'IM_Audit_LIVE',
@property = N'replicate_ddl',
@value = 1
GO
I tried to:
- restart the distribution agent
- restart the log reader agent
The synchronization monitor shows no replicated transactions available; the replication monitor is all good and the subscription validation passes.
the only thing i havent done is reinitialize the sunscription but with no snapshot i doubt this is a wise idea.
Anyone know if i have missed something or what i can check to see why it is not being picked up??
Kind regards,
Samuel
October 3, 2014 at 4:58 pm
EXEC sp_changepublication
@publication = 'IM_Audit_LIVE',
@property = N'replicate_ddl',
@value = 0
GO
You do realize that this statement sets replicate_ddl to false right?
October 6, 2014 at 2:25 am
i do now!!
must have been having a 'mare....cant think why i thought turning off ddl changes made sense!!
thanks 🙂
October 6, 2014 at 11:24 am
Yay! I'm glad it is working now!
October 7, 2014 at 3:13 am
yeah tested it with a few DDL statements and all good!!
thank 🙂
=================
mark as closed
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply