none
sp_addarticle replication

    Question

  • Hi Experts,

    Am trying to add article using TSQL script and normally follow the steps listed on this link to add from GUI.

    https://dba.stackexchange.com/questions/12725/add-article-to-transactional-publication-without-generating-new-snapshot

    Now, I scripted out an existing article which is like below 

    EXEC sp_addarticle 
         @publication = N'Publisher_Profile', 
         @article = N'Table', 
         @source_owner = N'dbo', 
         @source_object = N'Table', 
         @type = N'logbased', 
         @description = N'', 
         @creation_script = N'', 
         @pre_creation_cmd = N'drop', 
         @schema_option = 0x000000000803509F, 
         @identityrangemanagementoption = N'manual', 
         @destination_table = N'Table', 
         @destination_owner = N'dbo', 
         @status = 24, 
         @vertical_partition = N'false', 
         @ins_cmd = N'CALL  [sp_MSins_dboTable]', 
         @del_cmd = N'CALL  [sp_MSdel_dboTable]', 
         @upd_cmd = N'SCALL [sp_MSupd_dboTable]';
    
    GO

    How can I ensure the step View Snapshot Agent Status which I do from GUI is performed from TSQL so it just initializes the snapshot for the   newly added article not all the existing once's.

    Thanks 

    Priya

    • Moved by Tom Phillips Monday, November 12, 2018 5:56 PM Replication question
    Monday, November 12, 2018 4:52 PM

Answers

  • Before you run the script to add the new article, you need to set both publication properties of allow_anonymous and immediate_sync to false. After the article is added, execute sp_startpublication_snapshot to generate the new snapshot only for the newly added article. 

    A Fan of SSIS, SSRS and SSAS

    Monday, November 12, 2018 5:20 PM
  • A simple option is to put it in its own publication.

    you can also use sp_addsubscription and supply the table name in the @article parameter. You will need to fill in the publication name, the subscriber name

    and the Subscription db. Guoxiong approach may also work.

    Monday, November 12, 2018 6:18 PM
    Moderator

All replies

  • Before you run the script to add the new article, you need to set both publication properties of allow_anonymous and immediate_sync to false. After the article is added, execute sp_startpublication_snapshot to generate the new snapshot only for the newly added article. 

    A Fan of SSIS, SSRS and SSAS

    Monday, November 12, 2018 5:20 PM
  • A simple option is to put it in its own publication.

    you can also use sp_addsubscription and supply the table name in the @article parameter. You will need to fill in the publication name, the subscriber name

    and the Subscription db. Guoxiong approach may also work.

    Monday, November 12, 2018 6:18 PM
    Moderator