October 6, 2009 at 6:52 pm
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
October 9, 2009 at 1:19 pm
Run profiler when you add the atricles through the gui and see what code SQL is creating, then use that for the automation.
Andrew
October 9, 2009 at 1:30 pm
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
February 25, 2019 at 1:06 am
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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply