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


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)

INTRODUCTION

There are two ways to add an unpublished article to an existing snapshot, to a transactional publication, or to merge publication. You can either use SQL Server Enterprise Manager to add an unpublished article, or you can use the replication system stored procedures that are provided with SQL Server to add an unpublished article.

If you add an unpublished article to an existing publication by using SQL Server Enterprise Manager, the schema and the data of the added article is propagated automatically to all the subscribers when you run the respective replication agents for the first time after you add the article. If you add an article to a merge publication by using the sp_addmergearticle replication system stored procedure, the schema and the data of the added article is propagated automatically to all the subscribers. However, if you use the sp_addarticle replication system stored procedure to add an unpublished article to a snapshot or to a transactional publication, the schema and the data of the added article is not propagated automatically to all the subscribers when you run the respective replication agents for the first time after you add the article.

This article describes how to add an article to an existing snapshot or to a transactional publication by using the replication system stored procedures so that the schema and the data of the added article is also successfully propagated to the subscribers during the synchronization process.

For more information about how to add an article by using SQL Server Enterprise manager, see the following topic in SQL Server Books Online:

How to modify publications and articles (Enterprise Manager)

back to the top

MORE INFORMATION

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

To add an article to a snapshot or to a transactional publication, you can use the sp_addarticle replication system stored procedure. However, when you use the sp_addarticle stored procedure, the subscription is not automatically updated. Therefore, you must explicitly add the subscription to the new article. To do so, follow these steps:
  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.

      ParameterValue
      @publicationThe name of the current publication.
      @articleThe name of the new article.
      @source_objectThe name of the underlying table that is represented by the new article.
      @destination_tableThe name of the destination table that will be created at the subscriber.
      @force_invalidate_snapshot1 (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.

      ParameterValue
      @publicationThe name of the current publication.
      @articleThe name of the new article.
      @source_objectThe name of the underlying table that is represented by the new article.
      @destination_tableThe name of the destination table that will be created at the subscriber.
      @ins_cmdCALL sp_MSins_<Article Name>
      @del_cmdCALL sp_MSdel_<Article Name>
      @upd_cmdMCALL sp_MSupd_<Article Name>
      @force_invalidate_snapshot1 (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.

      ParameterValue
      @publicationThe name of the current publication.
      @articleThe name of the new article.
      @subscriberThe name of the subscriber.
      @destination_dbThe 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.

back to the top

REFERENCES

For more information about the replication system stored procedures that are used to add replication, see the following topics in SQL Server Books Online:

sp_addsubscription

sp_refreshsubscriptions

sp_addarticle



For more information about managing identity values, see the following topic in SQL Server Books Online:

Managing Identity Values



For additional information the sp_addmergearticle stored procedure, click the following article numbers to view the articles in the Microsoft Knowledge Base:

297369 FIX: Incorrect error occurs when you add merge article to a publication with NO SYNC Subscriber


269204 BUG: The addition of a merge article with a subset filter might cause the Snapshot Agent to fail

back to the top
Modification Type:MajorLast Reviewed:3/8/2004
Keywords:kbReplication kbHOWTOmaster kbCodeSnippet kbhowto KB830210 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.