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>'


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?


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 ,
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.

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.

  • a reoccurance of this today, to resolve I dropped the number of my subscribers down to 4 (we add extra ones in for peak trading months) and it wored fine, happen 4 times in a row, so its either a problem with 1 specific subscriber which blocks all subscribers or just having 6-7+ subscribers on SQL 2012 – DamagedGoods Jul 31 '13 at 8:05
  • Dropping and re creating publication worked for me . – Parassharma1990 Mar 13 '18 at 10:11

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.

  • Your sentence "If you script out subscriber details you can find the details" is a bit hard to understand... I'd suggest you try to clarify it. – joanolo Apr 25 '17 at 20:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.