script to add an article to replication process does not work - only works thru GUI - need help

  • Hello everybody:

    I have a transactional replication process going on. Want to add 1 more article to the replication process, need to do thru script (desired). Not GUI

    Here is the Script:

    use [UATPCDB201]

    exec sp_addarticle @publication = N'UATPCDB201_PUB', @article = N'pc_activity',@source_owner = N'dbo', @source_object = N'pc_activity', @type = N'logbased',@description = N'', @creation_script = N'', @pre_creation_cmd = N'drop',@schema_option = 0x000000000803509F, @identityrangemanagementoption = N'none',@destination_table = N'pc_activity', @destination_owner = N'dbo',@status = 24, @vertical_partition = N'false', @force_invalidate_snapshot = 1,@ins_cmd = N'CALL [dbo].[sp_MSins_dbopc_activity]', @del_cmd = N'CALL [dbo].[sp_MSdel_dbopc_activity]', @upd_cmd = N'SCALL [dbo].[sp_MSupd_dbopc_activity]'

    GO

    1.- I did stopped the Logreader job, run script, start snapshot job, start logreader job.

    Snapshot result: 2013-12-10 20:38:36.80 [0%] A snapshot was not generated because no subscriptions needed initialization.

    DOES NOT WORK.

    2.- If I add the article thru the GUI SSMS, Go to Publication, properties, articles, add 1 article, click OK. Run snapshot job only, and it works.

    Question:

    Why it works if I go thru GUI, and it does not work with Script?. what I am missing?.

    Please help.....

    Need scripts to deploy the addition of articles to the Production replication process.

    Thanks

    Pablo Campanini

  • You need to add a subscription on the subscriber before running the snapshot agent.

    eg

    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'

  • Andrew G (12/11/2013)


    You need to add a subscription on the subscriber before running the snapshot agent.

    eg

    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'

    Actually, you need to run this on the publisher only.

    What the OP has done is added the article to the publication. Theystill need to add a subscription to the article for the snapshot to be generated.

    Every new article requires sp_addarticle AND sp_addsubscription. Assuming you want all new articles to go to the subscriber you only need to run ap_addsubscription once regardless of how many new articles you add to a publication. Just use @article='all'. You can add them individually but in most cases you always want all new articles to go.

  • Be careful with this example. It includes far more than you need to add an article to an existing publication.

    You only need sp_addarticle and sp_addsubscription.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply