Adding new article to a publication

• juliaer

  SSC Enthusiast

  Points: 121

  More actions

  November 21, 2007 at 11:22 am

  #103434

  Hello everybody

  I'm new on Replication in Sql Server...

  I haver transactional replication working, and I have to add a table.

  I wonder how can I add a table to a publication that exists and this change don't affect to the data already replicated. I mean, I don't want to replicate all the data in the publication. I only want to add a table..

  Is anyway to do it without delete the data already replicate?

  Thanks in advance

• Suresh B.

  SSC-Insane

  Points: 22986

  More actions

  November 22, 2007 at 6:14 am

  #753385

  Right click on the publication you have created, select Properties, click Articles,

  clear the check box "Show only selected objects in the list"

  then select the table that you want to add to the replication...

  After adding the new article to the publication, reinitialize the subscription. Snapshot of the newly added article will be created. Existing articles will not be affected.

• Chris Becker

  SSCrazy

  Points: 2645

  More actions

  November 29, 2007 at 11:23 am

  #755581

  I have never been able to add an article with the GUI when there is an active subscription. I will test Suresh's post.

  You can do in script:

  exec sp_addarticle @publication = N'PubNameHere', @article = N'TableName', @source_owner = N'dbo'

  , @source_object = N'TableName', @destination_table = N'TableName', @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_TableName', @del_cmd = N'CALL sp_MSdel_TableName', @upd_cmd = N'MCALL sp_MSupd_TableName', @filter = null

  , @sync_object = null, @auto_identity_range = N'false'

  GO

  sp_addsubscription @publication = 'PubNameHere'

  , @article = 'TableName'

  , @subscriber = 'SubscrServerName'

  , @destination_db = 'DBName'

  , @sync_type = 'automatic'

  GO

  Then run snapshot - only new table will be pushed.

  ChrisB MCDBA

  MSSQLConsulting.com

  Chris Becker bcsdata.net

• Graeme100

  SSCrazy Eights

  Points: 8054

  More actions

  December 5, 2007 at 1:49 am

  #757248

  I would always do this via the scripts.

  Just a reminder, Suresh said reinitialize the subscription...you don't want to do that as that will require a new total snapshot.

  If you add an article through the GUI you will just need to run the snapshot and then distribute....please correct me if I'm wrong.

  G