Sql-server – 2012 Transactional replication not delivering new articles

replicationsql serversql-server-2012transactional-replication

I have added a new article to an existing push publication with the following code

sp_addarticle @publication='statics', article='FlightBaggageMarkup_tbl',source_object='FlightBaggageMarkup_tbl'

  EXEC sp_addsubscription
  @publication = 'Statics',
  @subscriber = 'SURVIVOR',
  @destination_db = 'MLT-Replicated'

this is a push subscription with immediate_sync switched off, running continuously instead of a schedule

run the snapshot agent and see it picks up just the new table, ok great. but the table and the custom stored procedures never appear at the subscriber and then replication falls over if you try to change a value in the source table as it cant find the stored procedure to call on the subscriber ? I have only seen this behavior since upgrading to 2012, but this maybe a coincidence

Best Answer

According to Define an Article:

If the publication has existing subscriptions and sp_helppublication returns a value of 0 in the immediate_sync column, you must call sp_addsubscription to add the article to each existing subscription.

After executing sp_addarticle, execute sp_addsubscription to add the article to each existing subscription. Then generate a new snapshot. The Distribution Agent should then copy the schema and data for the newly added article to the Subscriber(s).

Related Solutions

Sql-server – All pull subscriptions cannot connect to Distributor

This is a permissions issue, the Distribution Agent process account does not have rights on the distributor.

Verify the Distribution Agent process account is a member of the PAL, has read permissions on the snapshot share, and is a member of the db_owner fixed database role in the subscription database.

Sql-server – Transactional Replication snapshot locking Publisher db from beginning

To get this to work I scheduled switching off the 'immediate sync' to very low traffic period

EXEC sp_changepublication  
@publication = 'statics',
@property = 'Immediate_sync',
@value = 'false'
GO

I then ran a snapshot which only took a fraction of the time at 6 mins.

I then had to drop the current subscription for that article only

    EXEC sp_dropsubscription
    @publication = 'statics',
    @article = 'dbo.SupplierCorporatePayAccountLink_tbl',
    @subscriber = 'SURVIVOR'

and rerun the snapshot. Then i could add the subscription back into the publication

    EXEC sp_addsubscription
    @publication = 'statics',
    @article = 'dbo.SupplierCorporatePayAccountLink_tbl',
    @subscriber = 'SURVIVOR',
    @destination_db = 'MLT-Replicated',
    @reserved='Internal'

but it would not allow this without the fairly obscure setting

@reserved ='Internal'

or I got an error message saying I needed to subscribe to all articles.... replication then automatically sync'd the new table. I'm a happy man as this has been causing me some long nights!

Best Answer

Related Solutions

Sql-server – All pull subscriptions cannot connect to Distributor

Sql-server – Transactional Replication snapshot locking Publisher db from beginning

Related Question