Microsoft KB Archive/830210

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

Article ID: 830210

Article Last Modified on 4/23/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



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 

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:  Start SQL Server Enterprise Manager. In the left pane, locate and then select the instance of SQL Server that contains the publication. On the Tools menu, point to Replication, and then click Create and Manage Publications. In the Create and Manage Publications on  dialog box, locate and then select your publication. Click Script Publication. In the Generate SQL Script -  dialog box, under the Script options section, click to select the Script the steps to create this publication option, and then click Preview. In the Replication Component Script Preview dialog box, click Save As. In the Script File Location dialog box, type NewArticle.sql in the File Name box, and then click Save.</li> Start SQL Query Analyzer, and then connect to the instance of SQL Server that contains the publication.</li> 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. <ul>  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.

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. </li>  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.

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. </li></ul>

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.</li> Add subscriptions to the article that was added to your publication. <ul>  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.

You must also make sure that the parameters that can change the existing properties of the subscription cannot be modified. </li>  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 </li></ul>

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.</li> Run the Snapshot Agent that corresponds to the current publication at the distributor.</li> Run the Distribution Agent that corresponds to each subscription that subscribes to the current publication.</li></ol>

back to the top

<div class="references_section">