sp_addartcle fails

  • Roust_m

    SSCoach

    Points: 17199

    Hi,

    I am trying to automate replication creation. I am running the below code to add two articles to a publication. They both have the same name, but one is a view and another is a table. They belong to different schemas however. When I run the code I get an error:

    Msg 14030, Level 16, State 1, Procedure sp_MSaddschemaarticle, Line 115

    The article 'ErrorLog' exists in publication 'MyDB objects'.

    If I use GUI though it is ok to add both articles to the publication.

    Is there a way to change the code below to make it work?

    Thanks.

    use [MyDB]

    exec sp_addarticle @publication = N'MyDB objects',

    @article = N'ErrorLog', @source_owner = N'Operational',

    @source_object = N'ErrorLog', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop',

    @schema_option = 0x0000000008000001, @destination_table = N'ErrorLog',

    @destination_owner = N'Operational', @status = 16

    use [MyDB]

    exec sp_addarticle @publication = N'MyDB objects',

    @article = N'ErrorLog', @source_owner = N'Usage',

    @source_object = N'ErrorLog', @type = N'view schema only', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop',

    @schema_option = 0x0000000008000001, @destination_table = N'ErrorLog',

    @destination_owner = N'Usage', @status = 16

  • andrewkane17

    SSCertifiable

    Points: 7419

    Run profiler when you add the atricles through the gui and see what code SQL is creating, then use that for the automation.

    Andrew

  • Roy Ernest

    SSC-Dedicated

    Points: 38707

    You can just script the command that the GUI used using script method and see how it is being done. Probably the Schema qualifier is also added to the article name.

    -Roy

  • Roust_m

    SSCoach

    Points: 17199

    Found it out. In the script that SQL Server generates, the second article is passed as: @article = N'ErrorLog1'. So I just added the schema name to the article name, thus making @article parameter unique for the subscription.

    Thanks all for your feedback.

  • Daniel Adeniji-180202

    SSC Veteran

    Points: 204

    Roust_m:

    Worked for me, as well.

    Can't believe this is still an issue with MS SQL Server v2017.

    Great Thanks for sharing.

    Daniel Adeniji

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

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