3

I am trying to add new articles to an existing publication in SQL Server 2012.

I am adding the article to the publication with the syntax

--add the table into the correct publication
sp_addarticle @publication='<publicationname>', @article='CardBookingType_tbl', @source_object='CardBookingType_tbl'

Previously on SQL Server 2008 I used to use the following syntax to subscribe to 1 table only and then run the snapshot. This no longers work on SQL 2012 as you have to subscribe to all articles.

--add the table into the subscription
EXEC sp_addsubscription
        @publication = '<publicationname>',
        @subscriber = '<Servername>',
        @destination_db = '<dbname>'

GO

In SQL Server 2012, if I add the table via the GUI and then run the snapshot agent it picks up the new table fine. But if I add via the script it appears in the gui but the snapshot reports no new items were detected.

Seems like I am missing a piece of code to flag the subscribers as missing a article?

DamagedGoods
  • 2,591
  • 6
  • 34
  • 48

3 Answers3

1

Maybe Brandon's idea here will help: How to add an article to existing replication

Summary of his suggestions to fix:

  1. execute sp_refreshsubscriptions for pull subscriptions
  2. or in push subscriptions use the sp_addsubscription as you mentioned (worked for me)

I wrote some code to help identify the "orphaned articles on subscriber's":

--Run from distributor
SELECT  pub.data_source AS Publisher ,
        a.Publisher_db ,
        p.Publication ,
        sub.data_source AS Subscriber ,
        s.Subscriber_db ,
        a.Article
FROM   distribution.dbo.MSarticles as a
LEFT JOIN distribution.dbo.MSpublications AS p ON a.publisher_id = p.publisher_id AND a.publication_id = p.publication_id
JOIN sys.servers AS pub ON p.publisher_id = pub.server_id
LEFT JOIN distribution.dbo.MSsubscriptions AS s ON a.publisher_id = s.publisher_id AND a.publication_id = s.publication_id AND a.article_id = s.article_id
LEFT JOIN sys.servers AS sub ON s.subscriber_id = sub.server_id
WHERE s.subscriber_db IS NULL --Leave off for great documentation, but this will show the "orphaned" subscribers effected by this issue.
Community
  • 1
SQLGuyChuck
  • 166
  • 1
  • 6
0

I have experienced this several times now, where I have added a new article into an existing publication, added a subscription from all subscribers and then ran the snapshot agent on my remote distributor.

Sometimes the snapshot is not detected by any subscriber, sometimes by a apparently random selection of subscribers! I have not witnessed any pattern (I have 6 and its seems to happen on any of them from time to time).

I have not discovered the root cause, but the remedy is simple if not frustrating. Dropping the subscriptions from the subscribers that have not detected the new snapshot and re-adding the subscription before running the snapshot a 2nd time resolved the issue.

DamagedGoods
  • 2,591
  • 6
  • 34
  • 48
-1

I have faced similar issue.If you script out subscriber details you can find the details. If you find sp_addsubscription in parameters. If you find addsubscription only for specific articles then you need to modify parameter Articles as 'all' Then drop that subscription and recreate with modified script and reinitialize. It will work.

Arinjay
  • 1