We often add columns to replicated tables. How can we add a column without having to reinitialize the entire publication?

In SQL Server 2000, you can use the sp_repladdcolumn stored procedure to add a column to a replicated table without reinitializing the entire publication because the stored procedure automatically adds the column at the subscriber. For example, if the authors table in the Pubs database has already been published, you can add the newcol integer column to that table by executing the following stored procedure:

sp_repladdcolumn @source_object =
  'authors'
   , @column =  'newcol'
   , @typetext = 'INT'
   , @publication_to_add = '<name
      of publication authors is
      included in>'

Note that you can use the stored procedure sp_repladdcolumn to add only new columns to a replicated table; you can't use it to manage a table's existing columns. To drop existing columns from a published table, you can use the sp_repldropcolumn stored procedure.

End of Article


You must log on before posting a comment.

If you don't have a username & password, please register now.

Reader Comments

This is in regards to your article on the Feb 2003 Issue for InstantDoc #37513 which is "Adding Columns to Replicated Tables". After reading up on this in MS SQL Books online, they claim that the subscriber will not get updated if you add a new column using this command as mentioned below. Where as your article says that it will automatically add the column to the subscriber.Can someone validate this since it is not documented by Microsoft and whether this was updated thru a service pack. Read the Excerpt from SQL Books Online as pasted below: Remarks sp_repladdcolumn is used for all types of replication.

When using sp_repladdcolumn, if a schema change is made to an article that belongs to a publication that uses a DTS package, the schema change is not propagated to the Subscriber, and the custom procedures for INSERT/UPDATE/DELETE are not regenerated on the Subscribers. The user will need to regenerate the DTS package manually, and make the corresponding schema change at the Subscribers. If the schema update is not applied, the Distribution Agent may fail to apply subsequent modifications. Before making a schema change, make sure there are no pending transactions to be delivered. For more information, see How Transforming Published Data Works.

Timestamp and computed columns will be filtered out for character mode publications. If adding a timestamp or computed column using sp_repladdcolumn, subscriptions of such publications will not receive this new column.

Important A backup of the publication database should be performed after sp_repladdcolumn has been executed. Failure to do so can cause a merge failure after a restore of the publication database.

Ravi Nanda- January 28, 2003