In my case the sp_addarticle command looks like this (the PublicationName and TableName were changed to protect the innocent):
EXEC sp_addarticle @publication = N'PublicationName', @article = N'TableName', @source_object = N'TableName', @type = N'logbased', @pre_creation_cmd = N'drop', @destination_table = N'TableName';
Simply running sp_addarticle with the correct options was not enough to allow a snapshot to be generated for that table. When I started the Snapshot Agent, it would complete successfully with the message [0%] A snapshot was not generated because no subscriptions needed initialization. Perplexed, I decided to perform the same actions through the GUI (I knew it worked properly there) and then run SQL Server Profiler and filter by my local HostName.
Note: If you have enabled the @immediate_sync option (it is off by default), you will not be able to drop individual tables/articles from the publication. However, you can turn it back off without recreating the publication by issuing the following command:
EXEC sp_changepublication @publication = N'PublicationName', @property = N'immediate_sync', @value = N'FALSE'
In SQL Server Profiler, I saw sp_addarticle executed and I also observed several help SPROCs that the GUI uses for validation, but then the diamond in the rough appeared. There was an additional SPROC that was executed against the published database: sp_refreshsubscriptions. Executing sp_refreshsubscriptions will refresh the subscription metadata for any new articles added to the publication, which the Snapshot Agent subsequently uses to determine what articles need a new snapshot. The syntax is very simple (unlike sp_addarticle):
EXEC sp_refreshsubscriptions @publication = N'PublicationName';
Just replace the PublicationName with the name of the publication to which you have just added article(s) (you can add multiple articles and then execute sp_refreshsubscriptions only once). Once you execute it in the context of the published database, the next time the Snapshot Agent runs, it will generate a snapshot for the article(s).
Note: You do not need to run the sp_refreshsubscriptions SPROC when creating a new Subscription/Publication, as creating the Subscription after the Publication has been created populates all the article Subscription metadata from the Publication.
While I'm on the topic, I also want to quickly touch on how to drop a table from a Publication. There are two SPROCs to run to remove a table from the publication, sp_dropsubscription and sp_droparticle:
EXEC sp_dropsubscription @publication = N'PublicationName', @article = N'TableName', @subscriber = N'all', @destination_db = N'all';
EXEC sp_droparticle @publication = N'PublicationName', @article = N'TableName', @force_invalidate_snapshot = 1;
The final tip for today is that you can script starting the Snapshot Agent. It is another simple SPROC that you run against the published database:
EXEC sp_startpublication_snapshot N'PublicationName';
We use sp_startpublication_snapshot extensively when setting up or modifying a Publication (it sure beats finding the appropriate SQL Agent job on the Distributor, and we don't have to bring up Replication Monitor).
