none
Transactional Replication - Add Articles RRS feed

  • Question

  • Please help on this. Need T-SQL scripts.

    1. add/drop table without generating a snapshot using T-SQL

    2. add/drop view/stored procedure without generating a snapshot using T-SQL

    Thanks,

    Kumar

    Monday, May 27, 2019 12:23 PM

All replies

  • What value you have for Immediate_sync  on your publication?

    if false then for adding use sp_Addarticle  and then use sp_Addsubscription and use @sync_type ='replication support only'.

    https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addsubscription-transact-sql?view=sql-server-2017

    "Assumes that the Subscriber already has the schema and initial data for published tables."

    While dropping articles u not invalidating snapshot there should not be snapshot generation after dropping articles.



    Monday, May 27, 2019 1:05 PM
  • Thanks for your reply.

     Immediate_sync value as 1 on publication

    Can you provide scripts for add view/stored procedure without generating a snapshot using T-SQL


    Monday, May 27, 2019 1:50 PM
  • Hi KIRAN KUAMR

     

    >>add/drop view/stored procedure without generating a snapshot using T-SQL,

     

    You can use force invalidate option to void the snapshot while adding the article and re-initialize the subscriber with new snapshot. Even if you don't regenerate the snapshot, you need to have a new snapshot to reinitialize the subscriber. After adding an article to a publication, you must create a new snapshot for the publication (and all partitions if it is a merge publication with parameterized filters).

     

    [ @force_invalidate_snapshot = ] force_invalidate_snapshot Acknowledges that the action taken by this stored procedure may invalidate an existing snapshot. force_invalidate_snapshot is a bit, with a default of 0.

    0 specifies that changes to the article do not cause the snapshot to be invalid. If the stored procedure detects that the change does require a new snapshot, an error occurs and no changes are made.

    1 specifies that changes to the article may cause the snapshot to be invalid, and if there are existing subscriptions that would require a new snapshot, gives permission for the existing snapshot to be marked as obsolete and a new snapshot generated.

     

    For more details, please refer to http://blog.extreme-advice.com/2012/12/05/add-stored-procedure-in-transactional-replication-by-script-in-sql-server/ and https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-droparticle-transact-sql?view=sql-server-2017

     

    Hope this could help you .

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Tuesday, May 28, 2019 8:55 AM