How to add an article to an existing snapshot or to a transactional publication by using system stored procedures

Adding an article to an existing snapshot or to a transactional publication

1. Start SQL Server Enterprise Manager.
2. In the left pane, locate and then select the instance of SQL Server that contains the publication.
3. On the Tools menu, point to Replication, and then click Create and Manage Publications.
4. In the Create and Manage Publications on Instance Name dialog box, locate and then select your publication. Click Script Publication.
5. In the Generate SQL Script - <Publication Name> dialog box, under the Script options section, click to select the Script the steps to create this publication option, and then click Preview.
6. In the Replication Component Script Preview dialog box, click Save As.
7. In the Script File Location dialog box, type NewArticle.sql in the File Name box, and then click Save.
8. Start SQL Query Analyzer, and then connect to the instance of SQL Server that contains the publication.
9. To add an unpublished article to your publication, run Transact-SQL statements that are similar to the following in SQL Query Analyzer at the publisher on the publication database.
   • Code example for a snapshot publication
     

     USE <Publication Database>
     GO
     
     EXEC sp_addarticle 
     	@publication = N'<Publication Name>', 
     	@article = N'<Article Name>', 
     	@source_owner = N'dbo', 
     	@source_object = N'<Article Object Name>', 
     	@destination_table = N'<Destination Table Name>', 
     	@type = N'logbased', 
     	@creation_script = null, 
     	@description = null, 
     	@pre_creation_cmd = N'drop', 
     	@schema_option = 0x00000000000000F1, 
     	@status = 0, 
     	@vertical_partition = N'false', 
     	@ins_cmd = N'SQL', 
     	@del_cmd = N'SQL', 
     	@upd_cmd = N'SQL', 
     	@filter = null, 
     	@sync_object = null, 
     	@auto_identity_range = N'false'
     GO
     

     Note You must copy one of the Transact-SQL statements that contain the sp_addarticle replication system stored procedure that was used to add an article to the publication from the NewArticle.sql script file that was created in step 7.
     
     Make sure that you modify the following parameters as specified before you run the Transact-SQL statements.
     
     
     Collapse this tableExpand this table

     Parameter	Value
     @publication	The name of the current publication.
     @article	The name of the new article.
     @source_object	The name of the underlying table that is represented by the new article.
     @destination_table	The name of the destination table that will be created at the subscriber.
     @force_invalidate_snapshot	1 (If a snapshot is already generated for the publication.)

     
     
     Additionally, if your article contains an identity column and you want SQL Server to manage the identity range handling, you must set the @auto_identity_range parameter to true.
   • Code example for a transactional publication

     USE <Publication Database>
     GO
     
     EXEC sp_addarticle 
     	@publication = N'<Publication Name>', 
     	@article = N'<Article Name>', 
     	@source_owner = N'dbo', 
     	@source_object = N'<Article Object Name>', 
     	@destination_table = N'<Destination Table Name>', 
     	@type = N'logbased', 
     	@creation_script = null, 
     	@description = null, 
     	@pre_creation_cmd = N'drop', 
     	@schema_option = 0x00000000000000F3, 
     	@status = 16, 
     	@vertical_partition = N'false', 
     	@ins_cmd = N'CALL sp_MSins_<Article Name>', 
     	@del_cmd = N'CALL sp_MSdel_<Article Name>', 
     	@upd_cmd = N'MCALL sp_MSupd_<Article Name>', 
     	@filter = null, 
     	@sync_object = null, 
     	@auto_identity_range = N'false'
     GO

     
     Note You must copy one of the Transact-SQL statements that contain the sp_addarticle replication system stored procedure that was used to add an article to the publication from the NewArticle.sql script file that was created in step 7.
     
     Make sure that you modify the following parameters as specified before you run the Transact-SQL statements.
     
     
     Collapse this tableExpand this table

     Parameter	Value
     @publication	The name of the current publication.
     @article	The name of the new article.
     @source_object	The name of the underlying table that is represented by the new article.
     @destination_table	The name of the destination table that will be created at the subscriber.
     @ins_cmd	CALL sp_MSins_<Article Name>
     @del_cmd	CALL sp_MSdel_<Article Name>
     @upd_cmd	MCALL sp_MSupd_<Article Name>
     @force_invalidate_snapshot	1 (If a snapshot is already generated for the publication.)

     
     
     Additionally, if your article contains an identity column and you want SQL Server to manage the identity range handling, you must set the @auto_identity_range parameter to true.
   After you add the unpublished article to the current publication, you may notice that the Full Subscription property for each subscription that subscribes to the current publication is set to No:
   
   Full Subscription : No, this subscription contains only some of the articles in this publication.
10. Add subscriptions to the article that was added to your publication.
    • Code example for a push subscription
      
      For each push subscription that subscribes to the current publication, run the Transact-SQL statements in SQL Query Analyzer at the publisher on the publication database. Here is an example:

      USE <Publication Database>
      GO
      
      EXEC sp_addsubscription 
      	@publication = N'<publication Name>', 
      	@article = N'<Article Name>', 
      	@subscriber = N'<Subscriber Name>', 
      	@destination_db = N'<Destination Database>', 
      	@sync_type = N'automatic', 
      	@update_mode = N'read only', 
      	@offloadagent = 0, 
      	@dts_package_location = N'distributor'
      GO

      Note You must copy one of the Transact-SQL statements that contain the sp_addsubscription replication system stored procedure that was used to add the subscription for an article from the NewArticle.sql script file that was created in step 7.
      
      Make sure that you modify the following parameters before you run the Transact-SQL statement.
      
      
      Collapse this tableExpand this table

      Parameter	Value
      @publication	The name of the current publication.
      @article	The name of the new article.
      @subscriber	The name of the subscriber.
      @destination_db	The name of the destination database that contains the subscription.

      
      
      You must also make sure that the parameters that can change the existing properties of the subscription cannot be modified.
    • Code example for a pull subscription
      
      To add subscriptions to the new articles in the pull subscriptions for all the existing subscribers to the publication, run Transact-SQL statements in SQL Query Analyzer at the publisher on the publication database. Here is an example:

      USE <Publication Database>
      GO
      
      EXEC sp_refreshsubscriptions 
      	@publication = N'<Publication Name>'
      GO

    You may notice that the Full Subscription property for each subscription that subscribes to the current publication is set to Yes:
    
    Full Subscription : Yes, this subscription contains all articles in this publication.
11. Run the Snapshot Agent that corresponds to the current publication at the distributor.
12. Run the Distribution Agent that corresponds to each subscription that subscribes to the current publication.